vendredi 13 octobre 2017

Excel Userform call checkbox by name

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




Aucun commentaire:

Enregistrer un commentaire