Requiring Cell Input - Show Error Message

[Scott]

Hello,

I could use some help creating an error message within a spreadsheet.

Background: I have a spreadsheet that we use for company expense reports
(primarily travel). In cell A1, the user is supposed to input a department
where we'd charge the expense. In cell A2, the user is supposed to input a
purpose for their travel. Unfortunately, many of these expense reports we
recieve have cells A1 and A2 empty and this leads to a lot of work trying to
track down information.

What I'd like to do: When a user saves the spreadsheet, if cell A1 is empty,
I'd like a message box to appear that says "Please provide a department for
your expense." Similarly, if cell A2 is empty, I'd like a message box to
appear that says "Please provide a purpose for your trip." If both cells are
empty, I'd like a message box to appear that states, "Please provide a
department and purpose for this expense report."

If there's a better way to require user input for these cells, I'm also open
to other ideas. I appreciate your help.

Scott

 

[FSt1]

hi
try this...
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim r As Range
Dim ru As Range
Set r = Range("A1")
Set ru = Range("A2")
If IsEmpty(r) Then
MsgBox "Please enter a department number in A1."
Cancel = True
Exit Sub
End If
If IsEmpty(ru) Then
MsgBox "please enter a purpose in A2."
Cancel = True
End If
End Sub

this is workbook code so place it in the this workbook module.
it does not check for "correct data".
you may have to save the file, close and reopen before it works.

regards
FSt1