CODEWITHSUNDEEP
vbaif-statementmsgbox
The_Ltrain
The_Ltrain

Reputation: 13

VBA If statement to call Msgbox always calls msgbox

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

Upvotes: 1

Views: 138

Answers (2)

Chronocidal
Chronocidal

Reputation: 7951

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

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166306

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

Upvotes: 5

Related Questions