VBA Userform nested For loop

Question

I am trying to loop through 20 textboxes which is also included in a multipage. The condition that I have is I want to loop through 2 textboxes at a time based on the textboxname (Work1 , Completed1)

Example if the textbox name contains a 1 at the end then get values for both textboxes then loop to find the next one.

Code I have is below

Private Sub CheckBtn_Click()
    
    Dim crtl        As MSForms.Control
    
    'PAGE1
    UFA.MP1.Value = 0
    
    Dim txtctrl     As Control
    'loop through textboxes
    For Each txtctrl In Me.MP1.Pages(Me.MP1.Value).Controls
        
        If txtctrl.Name Like "Work*" Or txtctrl.Name Like "Completed*" Then        'check to make sure there is a value in the textbox
        If txtctrl.Text <> Empty And txtctrl.Value <> "0" Then
            
            Dim Task As String
            
            'GET TASK NAME
            If txtctrl.Name = "Work1" Or txtctrl.Name = "Completed1" Then
                Task = "Emails"
            ElseIf txtctrl.Name = "Work2" Or txtctrl.Name = "Completed2" Then
                Task = "New"
            ElseIf txtctrl.Name = "Work3" Or txtctrl.Name = "Completed3" Then
                Task = "Phone"
            End If
            
            MsgBox (Task & txtctrl.Name & txtctrl.Value)
            
        End If
    End If
    'Go to next textbox
Next
End Sub

The issue with this is the loop is finding the first Work1 name and then looping to then find the next but I want both values if they exist otherwise show just the values.

I don't see any loop at all in your code. But then, what do you mean with loop through 2 textboxes at all? How can you loop through a textbox? Also, it would help if you would indent your code, now it is really hard to read. — FunThomas
– FunThomas, Commented Oct 6, 2022 at 12:11
Thanks FunThomas, I have now indented my code. Its a For each loop but to explain in more detail, if 10 Textboxes have been filled in on the userform and the Checkbtn was clicked then the user would get a msgbox for every task. — New2Programming
– New2Programming, Commented Oct 6, 2022 at 12:38
A textbox control doesn't have both properties "text" and "value" like you wrote in your code above: txtctrl.Text <> Empty And txtctrl.Value <> "0" — VanGogh Gaming
– VanGogh Gaming, Commented Oct 6, 2022 at 12:46

AlexRivax · Accepted Answer · 2022-10-06 13:04:18Z

1

This will loop thru the Work* controls and if the Completed* has something, it will show both of them. If not it will only show the work* control data.

Private Sub CheckBtn_Click()

    Dim crtl As MSForms.Control
      
    'PAGE1
    UFA.MP1.Value = 0
    
    Dim txtctrl As Control
    
    For Each txtctrl In Me.MP1.Pages(Me.MP1.Value).Controls
                
        If txtctrl.Name Like "Work*" Then
            If txtctrl.Text <> Empty And txtctrl.Value <> "0" Then
                
                Dim Task As String
                Dim secondControlName As String
                Dim secondControlValue As String
                Dim secondControl As Variant
                
                secondControlName = ""
                secondControlValue = ""
                
                'GET TASK NAME
                If txtctrl.Name = "Work1" Or txtctrl.Name = "Completed1" Then
                    Task = "Emails"
                ElseIf txtctrl.Name = "Work2" Or txtctrl.Name = "Completed2" Then
                    Task = "New"
                ElseIf txtctrl.Name = "Work3" Or txtctrl.Name = "Completed3" Then
                    Task = "Phone"
                End If
                
                Set secondControl = Me.MP1.Pages(Me.MP1.Value).Controls.Item(Replace(txtctrl.Name, "Work", "Completed"))
                
                If secondControl.Text <> Empty And secondControl.Value <> "0" Then
                    secondControlName = secondControl.Name
                    secondControlValue = secondControl.Value
                End If
                
                MsgBox (Task & txtctrl.Name & txtctrl.Value & secondControlName & secondControlValue)
        
            End If
        End If
     Next

End Sub

Does this work for you?

answered Oct 6, 2022 at 13:04

AlexRivax

2141 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

New2Programming Over a year ago

AlexRivax Thankyou Sir! One small thing I hope, what if I want to pickup results if the work value is nothing but the complete does? Thanks again :)

AlexRivax Over a year ago

@New2Programming in that case you will need to remove the this IF line: If txtctrl.Text <> Empty And txtctrl.Value <> "0" Then and its End if too for sure. That way you will loop thur all the work controls no matter if they have data or not, but will show only both results when the complete has info.

Collectives™ on Stack Overflow

VBA Userform nested For loop

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related