VBA If statement to call Msgbox always calls msgbox

Question

I am trying to use an if statement to check for blanks and return a msgbox if there are blank fields. If there are no blank fields it runs another block of code. However even when you fill out all the fields the msgbox is always returned and the next block of code doesn't run. I'm pretty new to VBA and writing code in gneneral so any advice would be helpful.

Code in question:

'Check required fields
    If IsEmpty(C3) Then
        MsgBox ("Fill out all required fields")
    ElseIf IsEmpty(C7) = True Then
        MsgBox ("Fill out all required fields")
    ElseIf IsEmpty(C9) = True Then
        MsgBox ("Fill out all required fields")
    ElseIf IsEmpty(C11) = True Then
        MsgBox ("Fill out all required fields")
    ElseIf IsEmpty(C13) = True Then
        MsgBox ("Fill out all required fields")
    ElseIf IsEmpty(C17) = True Then
        MsgBox ("Fill out all required fields")
    Else

Are C3, C7 etc variables you've created, or did you mean something like `If Len(Range("C3").Value) = 0 Then` ? — Tim Williams, Apr 06 '20 at 15:50

score 5 · Accepted Answer · answered Apr 06 '20 at 15:54

You would reference a range like this:

If Len(Range("C3").Value) = 0 Then
    MsgBox "Fill out all required fields"

But shorter to do something like this:

If Application.CountA(Range("C3,C7,C11,C13,C17")) < 5 Then
    MsgBox "Fill out all required fields"
End if

score 1 · Answer 2 · answered Apr 06 '20 at 16:00

C7 is not a Range. Nor are C9, C11, C13 or C17

[C7] is a range. A better way to write this, however, would be ActiveSheet.Range("C7") or ActiveSheet.Cells(7,3)

You can then do nifty things like use Error Handling and SpecialCells:

On Error GoTo NoErrors
Dim BlankFields AS Long
BlankFields = ActiveSheet.Range("C7,C9,C11,C13,C17").SpecialCells(xlCellTypeBlanks).Count
MsgBox "Fill out all required fields" & vbCrLf & BlankFields & "field(s) remaining"
NoErrors:
On Error GoTo 0

That does look nifty! I'll have to look into that. – The_Ltrain Apr 06 '20 at 16:07 — The_Ltrain, Apr 06 '20 at 16:07

VBA If statement to call Msgbox always calls msgbox

2 Answers2