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
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
For Each cell In Selection
If cell.Text = "" Then
CellRef = 1
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
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!