VBA For-Loop Logic not making sense

[business_analyst]

Hello All,

I'm having a bit of a problem understanding the logic behind For statements in VBA and I was hoping someone could clear it up for me. I was basically trying to recognize if there were any blank textboxes in a userform, and assign different actions accordingly. Here is my code:

Private Sub check(ByVal frmname As Object)
    Dim ctl As Control
    Dim test As Integer
    
    test = 0
    
    If test = 0 Then
        For Each ctl In frmname.Controls
            If Left(ctl.Name, 4) = "txt_" Then
                If ctl.Value = "" Then
                    proceed = 0
                    Exit For
                Else: test = 1
                End If
            End If
        Next ctl
    ElseIf test = 1 Then
        proceed = 1
    End If
End Sub

The problem is, that the Elseif statement after the For loop is not being recognized. In other words, proceed = 1 is not being allocated for some reason. Why is only the proceed value within the For loop being read? How can I fix this issue? Any help would be greatly appreciated.

 

[Norie]

I don't actually think the problem is the For...Next, it's more likely the If...Else...Endif.

The logic of that is actually quite hard to follow.

When are you calling this code and where is proceed declared?

If all you want to do is check the textboxes for one userform I don't see why you need a separate sub.

 

[business_analyst]

I don't actually think the problem is the For...Next, it's more likely the If...Else...Endif.

The logic of that is actually quite hard to follow.

When are you calling this code and where is proceed declared?

If all you want to do is check the textboxes for one userform I don't see why you need a separate sub.

I am calling this sub within the Command Button of the userform. Within this CommandButton it is stating:

call check.Me
If proceed = 0 Then
        MsgBox = "Please fill out textboxes"
Elseif proceed = 1 Then
        MsgBox = "Thank You"

proceed is a public integer within the userform. The problem is that I am only getting proceed = 0 msgbox, even if all textboxes are filled.

 

[mjmact]

If I understand what the code does and what you want it to do correctly, it looks like your problem is with your initial If statement logic.

The If statement will only run code for at most one of the different criteria (or none if it doesnt match any If or ElseIf criteria and there is no Else statement).

If you meet the criteria for the initial "If" and then run its code, it will ignore all subsequent ElseIf and Else statements as a part of that overall If statement. So by meeting the criteria needed to run the for loop, you are excluding the possibility of running the code in the elseif portion. Even if the For loop changes so that the elseif statement evaluates to True, the elseif statement is completely ignored and not even evaluated.

 

[Norie]

I honestly don't see why you would need to use a separate sub for that, or the need for a public variable.

If you wanted to check that all textboxes are filled in you could try something like this.

Option Explicit
 
Private Sub CommandButton1_Click()
Dim ctl As MSForms.Control

    For Each ctl In Me.Controls
        If TypeOf ctl Is MSForms.TextBox Then
            If ctl.Value = "" Then
                MsgBox "There is missing data, please fill it in before trying to proceed."
                Exit Sub
            End If
        End If
    Next ctl
    
    ' code for whatever happens when the data is OK
    
End Sub

There might also be other approachs - checking each textbox using it's Exit event, disabling the command button until the conditions are satisfied etc