Requiring a value in a cell before allowing user to print

[GainesvilleWes via OfficeKB.com]

Good Morning,

I have an invoice that i would like to require a value in cell P2 prior to
the user printing the invoice. If no value is entered a message would pop up
reminding them to enter an invoice number.

Any assistance would be greatly appreciated.

Thank you,
Wes

 

[Gary''s Student]

Include the following event macro in the workbook code area:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If IsEmpty(Sheets("Sheet1").Range("P2")) Then
MsgBox ("Please fill P2 prior to printing")
Cancel = True
End If
End Sub


Because it is workbook code, it is very easy to install and use:

1. right-click the tiny Excel icon just to the left of File on the Menu Bar
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[Bob Phillips]

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Range("P2").Value = "" Then

MsgBox "You must enter a value in P2"
Cancel = True
End If
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

 

[GainesvilleWes via OfficeKB.com]

Thank you very much Gary and Bob.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Range("P2").Value = "" Then

MsgBox "You must enter a value in P2"
Cancel = True
End If
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

Good Morning,

[quoted text clipped - 7 lines]

Thank you,
Wes

 

[Gord Dibben]

If you're still out there an alternative is to have the Invoice Number
appear automatically without annoying the user.

See John McGimpsey's site for methods.

http://www.mcgimpsey.com/excel/udfs/sequentialnums.html


Gord Dibben MS Excel MVP

Thank you very much Gary and Bob.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Range("P2").Value = "" Then

MsgBox "You must enter a value in P2"
Cancel = True
End If
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

Good Morning,

[quoted text clipped - 7 lines]

Thank you,
Wes

 

[GainesvilleWes via OfficeKB.com]

I will take a look. Thank you.

If you're still out there an alternative is to have the Invoice Number
appear automatically without annoying the user.

See John McGimpsey's site for methods.

http://www.mcgimpsey.com/excel/udfs/sequentialnums.html

Gord Dibben MS Excel MVP

Thank you very much Gary and Bob.

[quoted text clipped - 16 lines]