Loop through controls in UserForm match control names

Jandrew63

New Member
Joined
Feb 9, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hi all,
First time poster , I'll try to be concise and follow the rules.
I'm trying to loop through entire userform and :
find all comboboxes then pare down their names
then find all checkboxes in a specific frame ,pare down those names and see if they match combobox names
if the combobox name matches the checkbox name ( and that checkbox is true)
then assign values to each of the corresponding ComboBoxes from a default combobox

In short , user enters value in default combobox , then chooses which of the other comboboxes will also have that value with a checkbox.
there are 3 default comboxes and 8 corresponding checkboxes /comboboxes for each.
The code runs without errors but the comboboxes are not getting values from defaults
Any help would really be appreciated ... not sure where things are going wrong?
VBA Code:
Private Sub Com2_Click()
Dim ctrl  As MSForms.Control
Dim ctrlType1 As String
Dim ctrlType2 As String
Dim ctrlType3 As String
ctrlType1 = "combobox"
ctrlType2 = " Checkbox"
ctrlType3 = " optionbutton"
Dim x1, y1, z, z1, z2, a, a1, a2, v, l, g, p, r As String

 For Each ctrl In Me.Controls
     If TypeName(ctrl) = ctrlType1 Then
     x1 = ctrl.Name
     z = Right(x1, 5) ' get last num of char to compare
     z1 = Right(x1, 7)
     z2 = Right(x1, 8)
     r = ctrl.Value
     End If
     
      If TypeName(ctrl) = ctrlType2 And Not TypeName(ctrl) = ctrlType3 Then
       y = ctrl.Name
       a = Right(y, 5) ' get last num of char to compare
       a1 = Right(y, 7)
       a2 = Right(y, 8)
       v = ctrl.Value
      End If
     If StrComp(z, a, vbTextCompare) = 0 And v = True Then 'combobox and checkbox names match and checkbox is checked
      r = CboLite1.Value ' transfer value to matching comboboxes
      End If
       If StrComp(z1, a1, vbTextCompare) = 0 And v = True Then
        r = CboGrills1.Value
        End If
         If StrComp(z2, a2, vbTextCompare) = 0 And v = True Then
          r = CboPrivacy1.Value
    
        End If
       
     
    Next ctrl
End Sub
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
The TypeName of a combobox is 'ComboBox' not 'combobox', also it should be 'OptionButton' not 'optionbutton'.
 

Jandrew63

New Member
Joined
Feb 9, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
I have corrected TypeNames . thanks
Still nothing . tried debug.print (r) after string comparison... nothing ?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
Have you tried setting a breakpoint on the sub heading with F9 and then stepping through the code using F8 when it's triggered?

P.S. Did you get rid of the spaces in ctrlType1, ctrlType2 and ctrlType3? Also, I didn't notice but you have ' Checkbox' instead of 'CheckBox.
 
Solution

Jandrew63

New Member
Joined
Feb 9, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
You pointed me in the exact direction i needed!! ... Setting breakpoint and stepping through really showed me what was going on ~
I redid the code as necessary and got it working just fine . thanks for you help :)

Private Sub Com2_Click()
' Sub AddCheckBoxesToLites()
Dim ctrl As MSForms.Control
Dim ctrl2 As MSForms.Control
Dim x1, y1, z, z1, z2, a, a1, a2, l, g, p, r, v As String
For Each ctrl In Me.FrameAddAll.Controls
If TypeOf ctrl Is MSForms.CheckBox Then
x1 = ctrl.Name
z = Right(x1, 5) ' get last num(s) of char to compare
z1 = Right(x1, 7)
z2 = Right(x1, 8)
v = ctrl.Value
End If
For Each ctrl2 In Me.Controls
If TypeOf ctrl2 Is MSForms.ComboBox Then
y = ctrl2.Name
a = Right(y, 5) ' get last num(s) of char to compare
a1 = Right(y, 7)
a2 = Right(y, 8)
If StrComp(z, a, vbTextCompare) = 0 And v = True Then 'combobox and checkbox names match and checkbox is checked
ctrl2.Value = CboLite1.Value ' transfer value to matching comboboxes
End If

If StrComp(z1, a1, vbTextCompare) = 0 And v = True Then
ctrl2.Value = CboGrills1.Value
End If
If StrComp(z2, a2, vbTextCompare) = 0 And v = True Then
ctrl2.Value = CboPrivacy1.Value
End If
End If

Next ctrl2

Next ctrl



End Sub
VBA Code:
 

Watch MrExcel Video

Forum statistics

Threads
1,130,190
Messages
5,640,743
Members
417,165
Latest member
Hilders1

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
Top