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?