Get Status of Check boxes on Userform

Question

I have many checkboxes and textboxes on my userform. I have these in groups as in, one textbox goes with two checkboxes. What I need to figure out is a short way to get the status of the checkboxes and the contents of the textbox so I can write this to a text file.

The info I will need to write to text would be any of the 25 textboxes: TextboxValue + (checkbox1 checked?) and/or (checkbox2 checked?) and so on...

Right now I only have this, but I have set approx 25 groups total, but not sure if groups are the right way to go. I hope I am making sense of this to all of you. Thanks

Dim ctrl As Control

For Each ctrl In Me.Controls

If TypeOf ctrl Is MSForms.CheckBox Then
  If ctrl.GroupName = "GroupA" Then
    If ctrl.Value = True Then
    MsgBox ctrl.Caption & ": " & Me.TextBox1.Text
    End If
 End If
End If

If TypeOf ctrl Is MSForms.CheckBox Then
  If ctrl.GroupName = "GroupB" Then
    If ctrl.Value = True Then
    MsgBox ctrl.Caption
    End If
 End If
End If
Next ctrl

Looks like a job for dynamic controls. I'd have a class responsible for managing a `TextBox` and two `CheckBox` MSForms controls, and then have it expose the `Text` of the textbox and the two `Boolean` properties abstracting away the checkboxes, and then all the form would need to care about would be 25 instances of that class, and you wouldn't need to iterate every single checkbox anymore. Why not use the textbox' name for the group? — Mathieu Guindon, Nov 13 '19 at 17:21
Groups are needed when all the controls have the same parent. If you put the textbox and its two checkboxes inside a `Frame` control, you don't need groups anymore. — Mathieu Guindon, Nov 13 '19 at 17:21
Or, consider naming the checkboxes after the textbox they're associated with: `FooBox` is associated with `FooCheckA` and `FooCheckB` - then you can dereference the checkboxes off the form's `Controls` collection, by name. — Mathieu Guindon, Nov 13 '19 at 17:37
Thanks Mathieu Guindon... would you post an example of how you would do that? I have been looking through examples but have not hit on the right one. — Noob2Java, Nov 13 '19 at 17:57
Unfortunately I don't have 2 hours to do this right now, but [this](https://stackoverflow.com/a/10224992/1188513) should get you started. — Mathieu Guindon, Nov 13 '19 at 18:04

score 0 · Accepted Answer · answered Nov 13 '19 at 18:31

0

With a consistent naming pattern you can do this:

Dim i As Long, tb As Control, chkA As Control, chkB As Control

For i = 1 To 25
    Set tb = Me.Controls("Grp_" & i & "_TB")
    Set chkA = Me.Controls("Grp_" & i & "_CHKA")
    Set chkB = Me.Controls("Grp_" & i & "_CHKB")

    'work with tb, chkA, chkB

Next i

answered Nov 13 '19 at 18:31

Tim Williams

154,628
8
97
125

Thanks Tim, I will try and see if I can make this work. – Noob2Java Nov 13 '19 at 18:51
How do I name the checkboxes? Do I create groups? – Noob2Java Nov 13 '19 at 19:19
No you just set their names in the Properties pane - "Grp_1_CHKA" , "Grp_1_CHKB", ..., "Grp_25_CHKA", Grp_25_CHKB" Similar deal for the textboxes "Grp_1_TB", ..., "Grp_25_TB" – Tim Williams Nov 13 '19 at 19:23
I got it.. I think this will work great. Thanks for the help! – Noob2Java Nov 13 '19 at 20:31

Get Status of Check boxes on Userform

1 Answers1