Save As with Cell Verification

[el zorro]

I have the ff macro which works great and saves the spreadsheet, but I need
an additional verfication before the macro is run:

Sub Save_As()
Dim objShell As Object
Dim objFolder As Object
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(&H5&)
Application.Dialogs(xlDialogSaveAs).Show _
objFolder.Self.Path & "\" & Range("f1").Value & ".xls"
End Sub

I need for the macro to verify either the group of cells A1, B1, C1 OR the
group of cells A1, B1, D1 be populated before the above macro is allowed to
run.
If neither of the two criteria are TRUE, then an error message window comes
up to say "Populate Required Fields" then give the user an option to Cancel
or Retry the operation.

Thanks much.

 

[Tom Ogilvy]

Sub Save_As()
set r = Range("A1:C1")
set r1 = Range("A1,B1,D1")
if application.CountA(r) <> 3 or _
application.CountA(r1) <> 3 then
Msgbox "Populate Required Fields", vbOK
exit sub
End if
Dim objShell As Object
Dim objFolder As Object
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(&H5&)
Application.Dialogs(xlDialogSaveAs).Show _
objFolder.Self.Path & "\" & Range("f1").Value & ".xls"
End Sub

--
Regards,
Tom Ogilvy


I need for the macro to verify either the group of cells A1, B1, C1 OR the
group of cells A1, B1, D1

 

[el zorro]

Tom,
It works great on single cells (not merged)...

However, my cell D1 is merged with E1 and the macro gives me the "msgbox"
window on the range r1 verification.

Is there a solution? Almost there but not quite.

Thanks.

 

[Tom Ogilvy]

I merged D1 and E1 and had data in A11 and it worked for me.

guess I am missing the specific situation you are having problems with.

 

[el zorro]

Tom,

I think I know where my logic was not properly explained.

If "r" is true (A1,B1 and C1 are populated), run the macro
OR
If "r1" is true(A1,B1 and D1 are populated) , run the macro; D1 is merged
with E1

BOTH "r" and "r1" cannot be true in my application because I have a data
validation in C1 and D1 that only either one can be populated but not both.

If "r" AND "r1" are both false, present the "msgbox".

The way I see the macro was created, BOTH "r" AND "r1" must be true before
the macro is run.

Thanks.