VBA For-Loop Logic not making sense

business_analyst

Board Regular
Joined
Jun 5, 2009
Messages
99
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:

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.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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.
 
Upvote 0
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:

Code:
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.
 
Upvote 0
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.
 
Upvote 0
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.
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top