Home > other >  VBA Userform nested For loop
VBA Userform nested For loop

Time:10-07

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.

CodePudding user response:

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?

  • Related