CODEWITHSUNDEEP
vbaexcelloopsfor-loop
silvio pontes
silvio pontes

Reputation: 25

Loop to check if TextBoxes are not empty

I try to For loop through all controls on a form to check if a TextBox is empty. Then puts focus on the first empty TextBox.

My Excel VBA Form has 34 TextBox. For now it has a nested If function that checks if all TextBoxes are not empty. If there are any empty .SetFocus on the 1st empty TextBox.

Sub ValidateForm()
  If TextBox1.Text = Empty Then
      TextBox1.SetFocus
  Else    
       If TextBox2.Text = Empty Then .........

Imagine this with 34 nesting?
So I tried the For Each loop solution. But it's not working! Any idea?

Sub ValidarForm5()
    For Each ctrl In UserForm1.Controls
        If TypeName(ctrl) = "TextBox" Then
            If ctrl.Text = Empty Then
                ctrl.SetFocus
            End If
        End If
    Next ctrl
End Sub

Upvotes: 2

Views: 10047

Answers (2)

CLR
CLR

Reputation: 12289

Your ValidarForm5 code works. I've tested it on a Userform without throwing any errors. @Peh has provided an solution in his comment but I thought I'd expand by explaining what your code is doing now.

You are looping through all 34 textboxes, one at a time. So you're testing TextBox1 to see if it is empty. If it is, then you SetFocus to that Textbox1. Then you move to TextBox2 and test that. If it's empty, then you SetFocus to that Textbox2.. and on.. and on..

So this means that if the last TextBox (Textbox34) is empty, then that will be the last one that has focus set to it. If that isn't empty, then it'll be the one before, etc.. etc..

What you're after though, is the first Textbox that is empty. As @Peh mentions, you just need to drop out of your loop once the first one is set. This is easily done with Exit For like so:

Sub ValidarForm5()
    For Each ctrl In UserForm1.Controls
        If TypeName(ctrl) = "TextBox" Then
            If ctrl.Text = Empty Then
                ctrl.SetFocus
                Exit For
            End If
        End If
    Next ctrl
End Sub

Upvotes: 2

T.M.
T.M.

Reputation: 9948

If your are using your code within the UserForm itself use Me instead of UserForm1 and declare your variables. If found an empty TextBox you have to Exit the For Loop, otherwise you won't remain in it.

Code in UserForm

Option Explicit    ' declaration head of your userform code module

Sub ValidateForm()
Dim ctrl As Object
For Each ctrl In Me.Controls
    If TypeName(ctrl) = "TextBox" Then
        If ctrl.Text = Empty Then
            ctrl.SetFocus
            Exit For    ' escape to remain in found textbox
        End If
    End If
Next ctrl
End Sub

Upvotes: 1

Related Questions