If-Else just stops

**doctormelodious** · 06-16-2007, 02:23 PM

Greetings,

I have the following field-validating VBscript in an Excel spreadsheet:

Function CheckFields() As String
    If Range("C2").Text = "" Then
        MsgBox "Please enter a date"
        Range("C2").Select
    ElseIf Range("C5").Text = "" Then
        MsgBox "Please enter a name for the client company"
        Range("C5").Select
    ElseIf Range("C8").Text = "" And Range("C12").Text = "" And Range("C13").Text = "" Then
        MsgBox "Please enter at least one phone number for the client" & vbCrLf & "(Main, Direct or Cell)."
        Range("C8").Select
    ElseIf Range("C11").Text = "" Then
        MsgBox "Please enter a name for the client contact"
        Range("C11").Select
    ElseIf Range("K3") = 1 Then
        If Range("C17").Text = "" Then
            MsgBox "Please enter a name for the radio station or agency"
            Range("C17").Select
        ElseIf Range("C20").Text = "" And Range("C23").Text = "" And Range("C24").Text = "" Then
            MsgBox "Please enter at least one phone number for the radio station or agency" & vbCrLf & "(Main, Direct or Cell)."
            Range("C20").Select
        ElseIf Range("C22").Text = "" Then
            MsgBox "Please enter a name for the radio station or agency contact"
            Range("C22").Select
        End If
    ElseIf Range("C30").Text = "" Then
        MsgBox "Please enter a name for the market"
        Range("C30").Select
    Else
        CheckFields = "true"
        MsgBox "OK"
    End If
End Function

When cell K3 = 1, the script checks other cells in the inner IF statement, and puts up MsgBoxes if certain conditions are true. This much works fine.

The problem is: if all of the conditions in the inner IF statement are false (i.e. all the cells have what they're supposed to have in them, and no MsgBoxes have to be put up) the outer IF statement just STOPS. The next part of it:

ElseIf Range("C30").Text = "" Then

is not executed. It's as though VBscript thinks the "End If" in the inner IF statment is part of the outer If statement (??).

Now, when cell K3 is not equal to 1, the entire outer IF statement is executed. So the problem is with the inner IF statment.

Any idea what the problem is?

Thanks!
DM

**antoka05** · 06-16-2007, 03:37 PM

Perhaps a solution is to use more 'If ... End If' statements and not 'If... Elseif... End If'.

This may be an idea (also if it's not elegant to read):

Function CheckFields() As String
    Dim checkOk As Boolean

    checkOk = True
    If Range("C2").Text = "" Then
        MsgBox "Please enter a date"
        Range("C2").Select
        checkOk = False
    End If
    
    If Range("C5").Text = "" _
        And checkOk = True Then
        MsgBox "Please enter a name for the client company"
        Range("C5").Select
        checkOk = False
    End If
    
    If Range("C8").Text = "" And Range("C12").Text = "" And Range("C13").Text = "" _
        And checkOk = True Then
        MsgBox "Please enter at least one phone number for the client" & vbCrLf & "(Main, Direct or Cell)."
        Range("C8").Select
        checkOk = False
    End If
    
    If Range("C11").Text = "" _
        And checkOk = True Then
        MsgBox "Please enter a name for the client contact"
        Range("C11").Select
        checkOk = False
    End If
    
    If Range("K3") = 1 _
        And checkOk = True Then
        If Range("C17").Text = "" Then
            MsgBox "Please enter a name for the radio station or agency"
            Range("C17").Select
            checkOk = False
        ElseIf Range("C20").Text = "" And Range("C23").Text = "" And Range("C24").Text = "" Then
            MsgBox "Please enter at least one phone number for the radio station or agency" & vbCrLf & "(Main, Direct or Cell)."
            Range("C20").Select
            checkOk = False
        ElseIf Range("C22").Text = "" Then
            MsgBox "Please enter a name for the radio station or agency contact"
            Range("C22").Select
            checkOk = False
        End If
    End If
    
    If Range("C30").Text = "" _
        And checkOk = True Then
        MsgBox "Please enter a name for the market"
        Range("C30").Select
        checkOk = False
    End If
    
    If checkOk = True Then
        CheckFields = "true"
        MsgBox "OK"
    End If
End Function

Regards,
Antonio

**doctormelodious** · 06-16-2007, 05:26 PM

Hi Antonio,

I thought about that very thing after I posted, and tried it. But the problem is, I want to stop after the first MsgBox. Give the user a chance to go back and fill in that one cell. By making separate IF statements. It fires off one MsgBox after another.

Or is there a way to EXIT a function? Like the "return" statement in Perl, for example.

Thanks much for your reply,
DM