CODEWITHSUNDEEP
excelvba
stacson76
stacson76

Reputation: 15

If Statement for Error Checking is not working

I need to do some error checking so a user knows they have missed some information before sending the form.

I have a table which is controlled by a Data Validation list dropdown box. This box has numbers 1 to 15, and will show/hide rows associated to the number selected i.e. 3 selected, so rows 1 to 3 are displayed, 4 to 15 or hidden. What the error checking code is supposed to do is, check the list number selected, then check a cell in each row to see if it is empty or not, if empty display the error message, if not then do nothing. I need this error message to display for each row which is displayed and not for the hidden rows. I have a cell at the top of the page with tells the user how many errors they have, so I don't want the hidden rows adding to this count. I can get it working for numbers zero and 1, but for 2 to 15 it is not working. Code below only shows numbers 0 to 3, I can alter the code for the other rows once working.

Correct outcome when nbr 3 is selected D63 is empty so error msg in A63, D64:65 not empty so no error msg Error

Actual outcome when Nbr 3 is selected D63:65 are empty so A63:65 should display the error msg Outcome

Sub Check_SRU_Table()

    ' SRU 0 ** WORKING **
    If Range("SRUAdd").Value = "0" Then
        Range("Sig_1").Value = "Signature Release 1"
        Range("Sig_2").Value = "Signature Release 2"
        Range("Sig_3").Value = "Signature Release 3"
    ' 4 TO 15 OMITTED
    End If


    ' SRU 1 ** WORKING **
    If Range("SRUAdd").Value = "1" Then
        If Range("SRUName1").Value = "" Then
            Range("Sig_1").Value = "Signature Release 1 - ERROR"
        Else
            Range("Sig_1").Value = "Signature Release 1"
        End If
    End If

    ' SRU 2 ** NOT WORKING **
    If Range("SRUAdd").Value = "2" Then
        If Range("SRUName1").Value = "" Then
            Range("Sig_1").Value = "Signature Release 1 - ERROR"
        ElseIf Range("SRUName2").Value = "" Then
            Range("Sig_2").Value = "Signature Release 2 - ERROR"
        Else
            Range("Sig_1").Value = "Signature Release 1"
            Range("Sig_2").Value = "Signature Release 2"
        End If
    End If

    ' SRU 3 ** NOT WORKING **
    If Range("SRUAdd").Value = "3" Then
        If Range("SRUName1").Value = "" Then
            Range("Sig_1").Value = "Signature Release 1 - ERROR"
        ElseIf Range("SRUName2").Value = "" Then
            Range("Sig_2").Value = "Signature Release 2 - ERROR"
        ElseIf Range("SRUName3").Value = "" Then
            Range("Sig_3").Value = "Signature Release 3 - ERROR"
        Else
            Range("Sig_1").Value = "Signature Release 1"
            Range("Sig_2").Value = "Signature Release 2"
            Range("Sig_3").Value = "Signature Release 3"
        End If
    End If
    End Sub

    Private Sub BtnCheck_Click()
        Check_SRU_Table
    End Sub

Any help to figure out where I'm going wrong would be much appreciated.

Upvotes: 0

Views: 60

Answers (2)

rskar
rskar

Reputation: 4657

Your "SRU 2" and "SRU 3" are not working because If ... ElseIf ... ElseIf ... (a.k.a. an if-else-if ladder) will only capture the first error encountered. I think you over-thought it.

' SRU 2 ** Try something like this **
If Range("SRUAdd").Value = "2" Then
    If Range("SRUName1").Value = "" Then
        Range("Sig_1").Value = "Signature Release 1 - ERROR"
    Else
        Range("Sig_1").Value = "Signature Release 1"
    End If
    If Range("SRUName2").Value = "" Then
        Range("Sig_2").Value = "Signature Release 2 - ERROR"
    Else
        Range("Sig_2").Value = "Signature Release 2"
    End If
End If

Upvotes: 1

Marc
Marc

Reputation: 11633

Only one condition of your If...ElseIf...Else block will be met. Sounds like you want to check all conditions. Therefore, you'd need to rewrite to something like this:

If Range("SRUAdd").Value = "3" Then
    If Range("SRUName1").Value = "" Then
        Range("Sig_1").Value = "Signature Release 1 - ERROR"
    End If
    If Range("SRUName2").Value = "" Then
        Range("Sig_2").Value = "Signature Release 2 - ERROR"
    End If
    If Range("SRUName3").Value = "" Then
        Range("Sig_3").Value = "Signature Release 3 - ERROR"
    End If
    If Range("SRUName3").Value <> "" And Range("SRUName2").Value <> "" And Range("SRUName1").Value <> "" Then
        Range("Sig_1").Value = "Signature Release 1"
        Range("Sig_2").Value = "Signature Release 2"
        Range("Sig_3").Value = "Signature Release 3"
    End If
End If

I'm sure there's a more elegant way to do it, but this version requires the least rewrite of how you've chosen to approach it.

Upvotes: 1

Related Questions