MsgBox for unmet conditions (?)

[Soozie]

Hey! I'm looking for a way to have a message box appear when a person tries to email, save and/or print a worksheet AND they haven't filled in all required fields. The fields are scattered throughout the sheet (ie: A1,B50,C12)
PLEASE help. This is driving me nuts.

 

[Juan Pablo González]

Save and Print are "easy", but Email... that's a complicated one. I feel that the easy way out would be to replace the current menus with your own, and test, when they are run, if they have filled the info.

To do Save and Print you use the Workbook events, BeforeSave and BeforePrint


_________________
Regards,

Juan Pablo G.
MrExcel.com Consulting
This message was edited by Juan Pablo G. on 2002-02-20 11:54

 

[Soozie]

Ok. I thought I could keep up but quite obviously I'm a novice. Can you give me a specific...in other words could I beg you enough to actually spell it out for me? I'd be grateful forever.

 

[Juan Pablo González]

Ok, let's take it step by step then.

First, create this function in a module

Function Validate() as Boolean
Dim Rng as Range
Dim Cll as Range

Validate = True 'This is the default
Set Rng = Union(Range("A1"),Range("B50"),Range("C12"))

For each Cll in Rng
If Len(Cll) = 0 then 'Cell is Empty
Validate = False
Exit Function
End If
Next Cll
End Function

And in your code you can use it like this:

Sub Test()
If Not Validate Then MsgBox ("Hey !, FILL ME UP")
End Sub

This is the first step. Tell me when you have it completed.

_________________
Regards,

Juan Pablo G.
MrExcel.com Consulting
This message was edited by Juan Pablo G. on 2002-02-20 11:59

 

[Soozie]

Ok. I'm with you so far...

 

[Juan Pablo González]

Ok, now right click on the little workbook icon near the "File" menu, and select view code. That should take you to the VB Editor.

Put this there.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Not Validate Then
MsgBox "Hey, FILL ME UP !"
Cancel = True
End If
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Not Validate Then
MsgBox "Hey, FILL ME UP !"
Cancel = True
End If
End Sub

That way, the user will not be able to Save or Print unless they fill the values.

 

[Soozie]

Ah JUAN! You are the true GuRu! Thanks a million!