CODEWITHSUNDEEP
excelvba
Hashmat Habibzadah
Hashmat Habibzadah

Reputation: 45

Stop Macro if MsgBox appears

I have a Macro that performs a few validations. I've simplified the code below for illustration.

How could I tweak my code to make this possible? Thank you for helping me!

Sub blank()

'Validations
If Cells(10, 4).Value = "" Then
MsgBox "Please fill in Invoice Date"
End If

If Cells(11, 4).Value = "" Then
MsgBox "Please fill in Invoice Number"
End If

'Save As
ActiveWorkbook.SaveAs filename:="Lease Admin Processing Input Form"

End Sub

Upvotes: 0

Views: 881

Answers (3)

Tim Williams
Tim Williams

Reputation: 166126

You can do this:

Sub blank()

    If Cells(10, 4).Value = "" Or Cells(11, 4).Value = "" Then
        MsgBox "Please fill in both Invoice Number and Invoice Date"
    Else
        ActiveWorkbook.SaveAs filename:="Lease Admin Processing Input Form"
    End If

End Sub

Or this if you have many checks:

Dim Msg As String

CheckEmpty Cells(10, 4), Msg, "Invoice date is required"
CheckEmpty Cells(11, 4), Msg, "Invoice number is required"
'...more checks

If len(Msg) > 0 Then
    MsgBox "Please correct the following items:" & vblf & Msg
Else
    'save etc
End If

'....

'utility sub for building notification message
Sub CheckEmpty(c as Range, byRef Msg as string, errMsg As String) 
    If Len(Trim(c.Value)) = 0 Then
        Msg = Msg & vbLf & " - " & errMsg
    End If
End If

Upvotes: 2

Chris
Chris

Reputation: 943

Maybe like this

Sub blank()

Dim bolChecker as boolean

bolChecker = false

'Validations
If Cells(10, 4).Value = "" Then
MsgBox "Please fill in Invoice Date"
bolChecker = true
End If

If Cells(11, 4).Value = "" Then
MsgBox "Please fill in Invoice Number"
bolChecker = true
End If

'Save As
if bolChecker = false then ActiveWorkbook.SaveAs filename:="Lease Admin Processing Input Form"

End Sub

Upvotes: 1

Wizhi
Wizhi

Reputation: 6549

Just check if both the validations are true. If they both are true then run the Save As part, otherwise it will skip to save as :)

Sub blank()

'Validations
If Cells(10, 4).Value = "" Then
MsgBox "Please fill in Invoice Date"
End If

If Cells(11, 4).Value = "" Then
MsgBox "Please fill in Invoice Number"
End If

If Cells(10, 4).Value = "" And Cells(11, 4).Value = "" Then
'Save As
ActiveWorkbook.SaveAs filename:="Lease Admin Processing Input Form"
End If
End Sub

Upvotes: 0

Related Questions