Excel Userform call checkbox by name

Question

I have a form that sets up a list of labels with content and an accompanying checkbox on initialisation.

I want to check the value of a checkbox when a button is clicked.

How do I reference back to the checkbox - I have called the checkbox a number (the value of i) when they are created.

Code to add the checkbox:

Sub addLabel()
    Dim theCheck As Object
    Dim theLabel As Object
    Dim i As Long
    Dim LastRow As Integer

    LastRow = Worksheets("Assumptions").Cells(Rows.Count, "B").End(xlUp).Row

    For i = 1 To LastRow
        Set theLabel = UserForm1.Controls.Add("Forms.Label.1", "Assumption" & i, True)
        With theLabel
            .Name = "Assumption" & i
            .Caption = Worksheets("Assumptions").Range("B" & i).Value ' &    labelCounter
            .Left = 156
            .Width = 500
            .Top = 138 + i * 20
        End With
        Set theCheck = UserForm1.Controls.Add("Forms.CheckBox.1", i, True)
        With theCheck
            .Name = i
            .Left = 140
            .Width = 10
            .Top = 138 + i * 20
        End With
    Next
End Sub

My ultimate goal is to check which checkbox is 'True' and then IF true enter the accompanying label content into a worksheet.

My main struggle at the moment is how to reference the checkboxes by name (e.g. loop through them all where they are called 1-10 for example.

Thanks

Teasel · Accepted Answer · 2017-10-13 11:53:47Z

1

To make reference of an object in your form you can use the following syntax

<Name of your form>.<Name of your control>

In you can I believe that something like UserForm1.1 but this is not a great idea to call your checkbox only with a number, give it with a proper name.

I strongly recommend that you change

With theCheck
    .Name = i 'This is not great
    .Left = 140
    .Width = 10
    .Top = 138 + i * 20
End With

By something more explicit like

With theCheck
    .Name = "cb" & i 'Not great but better
    .Left = 140
    .Width = 10
    .Top = 138 + i * 20
End With

Loop through each Checkbox in your form

To go through each Checkbox and check if it's checked or not, you can use something like this

'Go through each control in your UserForm
For Each myControl In UserForm1.Controls
    'If the current control is a Checkbox
    If (TypeName(myControl) = "Checkbox") Then
        'Check it's value
        If (myControl.Value = True) Then
            'Do whatever you want
            'You can access your checkbox properties with myControl.YOUR_PROPERTY
        End If
    End If
Next myControl

edited Oct 13, 2017 at 11:53

answered Oct 13, 2017 at 11:45

Teasel

1,3404 gold badges19 silver badges25 bronze badges

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

5 Comments

QHarr Over a year ago

can you not simplify to If TypeName(myControl) = "Checkbox" and myControl.Value = True Then.....

Teasel Over a year ago

@QHarr No you can't. If you try to you'll get an error saying this property or method is not supported.

QHarr Over a year ago

Any idea why that is the case?

Teasel Over a year ago

@QHarr Because a Control Object has no Value property

Chris Peh Over a year ago

Do I have to declare myControl beforehand with Dim? If I renamed my UserForm to "Opt" in name, I thought to change to Opt.Controls - but it gives me an error... Using UserForm.Controls runs into debugging 424 error. I'm lost (my Checkboxes are named "CheckBox_" & i

Collectives™ on Stack Overflow

Excel Userform call checkbox by name

1 Answer 1

Loop through each Checkbox in your form

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Loop through each Checkbox in your form

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related