Cell validation (IsEmpty) and returning MsgBox

[kungfauxn00b]

Hey guys and gals,

I have the following code which checks multiple ranges for blank values and returns a MsgBox for each cell when IsEmpty = TRUE

Sub Check_Event_Form()

    Dim cell As Range

    For Each cell In Range("D10,D14,D18,D28,D41,D44,D46,D48,D53,D56,D73,AH73")
        If IsEmpty(cell) Then
            MsgBox (cell.Offset(0, -2).Value & " is blank")
        Else
            MsgBox ("All required fields have been entered")
        End If
    Next

End Sub

But, as the the Else MsgBox is included in the For loop, the MsgBox is returned after each check of a cell.

Any suggestions on how I can check all cells and if they all return FALSE, show the all good message?

And for a Brucey-bonus, is there any (easy) way I can show all errors in one MsgBox instead after each check?

Huge thanks in advance!

(Please go easy on me, I'm not great with VBA!)

 

[Fluff]

How about

Sub Check_Event_Form()

    Dim Cl As Range
    Dim Msg As String

    For Each Cl In Range("D10,D14,D18,D28,D41,D44,D46,D48,D53,D56,D73,AH73")
        If IsEmpty(Cl) Then Msg = Msg & Cl.Offset(0, -2).Value & " is blank" & vbLf
    Next Cl
    If Len(Msg) = 0 Then Msg = "All required fields have been entered"
    MsgBox Msg

End Sub

I would recommend against using VBA keywords as variables, because it can cause problems (hence I've renamed your cell variable)

 

[kungfauxn00b]

Perfect, thank you!

 

[Fluff]

Glad to help & thanks for the feedback