Google Answers Logo
View Question
Q: Macros code to set Data Validation in Microsoft Excel with Message Box ( No Answer,   0 Comments )
Subject: Macros code to set Data Validation in Microsoft Excel with Message Box
Category: Computers > Algorithms
Asked by: jesse82-ga
List Price: $2.00
Posted: 09 Nov 2005 21:20 PST
Expires: 18 Nov 2005 10:45 PST
Question ID: 591324
My workbook has 5 worksheets. I am trying to set range C4 to C200 as
conditionally required fields but only in my worksheet 3, which has
been renamed CCA 3.

Id like to set a condition/rule in CCA 3 worksheet that if upon saving
there is a value in either A4 or B4 BUT there is no corresponding
value in C4, message box will appear saying "Must enter AGE in
<specify here rows not meeting the criteria>. Do you want to

Message need NOT appear for the number of times that criteria is not
met. Just once (because for the user, it may be valid that column C
really has no value). At least in that one warning message, the
relevant rows have already been enumerated.

This rule will look into the range from ROW 4 up to ROW 200 (i.e. if
there is any value in either A199 or B199 but none in C199, that one
message box will appear.)

However, even if the condition is not met, user must still be able to
save workbook if he chooses YES to "Do you want to continue?" in the
message box.
So the message is only a warning.

My current (erroneous) code which I placed under "This Workbook" in VBA is the ff:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 
Dim cell As Range, Msg, Style, Title, Response, CellRef 
CellRef = 0 
Sheets("CCA 3").Select 
For Each cell In Selection 
If cell.Text = "" Then 
CellRef = 1 
GoTo Finish 
End If 
Next cell 

If CellRef = 1 Then 
Msg = "Must enter AGE in <specify here rows not meeting the criteria>.
Do you want to continue?"
Style = vbYesNo 
Response = MsgBox(Msg, Style) 
If Response = vbNo Then 
Cancel = True 
Exit Sub 
End If 
End If 
End Sub 

Please modify this code and kindly give me instructions to yield desired outcome. 

Just a little help please:) Thanks!

If this can be done using FORMULA instead of macros, that will be
better since user sometimes click on "Disable Macros." TY!
There is no answer at this time.

There are no comments at this time.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy