VBA obtaining checkbox values from userform

Question

I'm having trouble obtaining values from checkboxes of a userform. The problem I have is that the userform creates a variable number of checkboxes based on a value from a sheet. The code for this:

Private Sub UserForm_Initialize()

Dim LastRow As Long
Dim i As Long
Dim Teller As Long
Dim chkBox As MSForms.CheckBox

Teller = 1
LastRow = Worksheets("Sheet").Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To LastRow
    If Worksheets("Sheet").Cells(i, 1).Value = Worksheets("Sheet").Range("S1").Value Then
        Set chkBox = Me.Controls.Add("Forms.CheckBox.1", "CheckBox_" & Teller)
        chkBox.Caption = Worksheets("Sheet").Cells(i, 9).Value
        chkBox.Left = 5
        chkBox.Top = 25 + ((Teller - 1) * 20)
        Teller = Teller + 1
    End If
Next i    
End Sub

So this creates a number of checkboxes named CheckBox_1, CheckBox_2 etc. The problem is when I try to get the value for CheckBox_1 in the module, CheckBox_1 has not yet been created so I'm not able to use it.

Dim x as String
With UserForm4
     .Show
     x = .CheckBox_1
     MsgBox (x)
     End
End With

Derek · Accepted Answer · 2017-07-21 09:14:51Z

1

You'll need to loop through .Controls the textbox is not a property on your form.

answered Jul 21, 2017 at 9:14

Derek

9,0837 gold badges40 silver badges65 bronze badges

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

1 Comment

YowE3K Over a year ago

Won't necessarily have to loop through the controls - x = .Controls("CheckBox_1").Value will work if the OP knows that they want the first one. (But obviously x = .Controls("CheckBox_" & i).Value can be used if they do need to loop through.)

Collectives™ on Stack Overflow

VBA obtaining checkbox values from userform

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related