Return the value from a checklist on a UserForm

Question

This makes a UserForm checklist and it works.

Sub UserForm_Initialize()

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

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

For i = 1 To LastRow
    Set chkBox = Me.Controls.Add("Forms.CheckBox.1", "CheckBox_" & i)
    chkBox.Caption = Worksheets("Sheet1").Cells(i, 1).Value
    chkBox.Left = 5
    chkBox.Top = 5 + ((i - 1) * 20)
Next i

End Sub

I want to select as many of the boxes as I need, hit a command button and have the resulting values pasted in a different cell.

For example I have 1-10 in cells A1-A10. The first piece makes a checklist for each value 1-10. If I check the boxes next to 2, 3, 5, and 7, hit the command button and then want 2, 3, 5, and 7 to be entered into cells G2, G3, G5 and G7.

I cant figure out how to get this last part to happen. I have tried to make If statement

Sub CommandButton1_Click()
    If chkBox1 = False Then GoTo Here
    Else
        Range("G1").Value = Me.TextBox1.Text
    End If
    Here
End Sub

I get

"Compile error: Variable not defined"

I tried different names instead of "chkbox1" but get the same error.

Answer 1

I think this does what you want.

You can reference the controls using their name and loop through them in a similar way to the Initialize code. By declaring 'LastRow` before the sub we can use it in both subs.

Dim LastRow     As Long

Private Sub CommandButton1_Click()

Dim i As Long

For i = 1 To LastRow
    If Me.Controls("CheckBox_" & i) Then
        Range("G" & i).Value = i
    End If
Next i

End Sub

Sub UserForm_Initialize()

Dim i           As Long
Dim chkBox      As MSForms.CheckBox

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

For i = 1 To LastRow
    Set chkBox = Me.Controls.Add("Forms.CheckBox.1", "CheckBox_" & i)
    chkBox.Caption = Worksheets("Sheet1").Cells(i, 1).Value
    chkBox.Left = 5
    chkBox.Top = 5 + ((i - 1) * 20)
Next i

End Sub