VBA - problem with recognized variable in event handler

Question

VBA - problem with recognized variable in event handler.

in the event handler (click) i can't get the program to recognize variable j (it's local). when it get to the line: check.Value = MyCheck & j.Value it return : 'Mycheck1' only, even though its in a loop. i need the program to go throw all of the check buttons and check if they are chosen. does anybody know the right syntax?

main sub:

With TempForm.CodeModule
    ' ** Add/change next 5 lines' This code adds the commands/event handlers to the form
    X = .CountOfLines
    .InsertLines X + 1, "Sub CommandButton1_Click()"
    .InsertLines X + 2, "Dim rs As ADODB.Recordset"
    .InsertLines X + 3, "Dim rst As ADODB.Recordset"
    .InsertLines X + 4, "Dim i As Integer"
    .InsertLines X + 5, "Dim ran as string"
    .InsertLines X + 6, "j = 1 "
    .InsertLines X + 7, "set rs = Rsfun"
    .InsertLines X + 8, "Do While Not rs.EOF"
    '.InsertLines X + 8, "For j = " & i & " To rs.MaxRecords"
    '.InsertLines X + 9, "" & i & " = " & i & " +1"
    .InsertLines X + 9, "Dim check As MSForms.CheckBox "
    .InsertLines X + 10, "Set check = UserForm1.Controls.Add(""Forms.checkbox.1"")"
    .InsertLines X + 11, "check = MyCheck & j"
    .InsertLines X + 12, "If Check.value = true then"
    .InsertLines X + 13, "ran = ""A" & i & ""
    .InsertLines X + 14, "MsgBox (ran)"
    .InsertLines X + 15, "range(ran).value= rs.Fields(0)"
    .InsertLines X + 16, "End If"
    .InsertLines X + 17, "RS.MoveNext"
    .InsertLines X + 18, "j= j+1"
    .InsertLines X + 19, "Loop"
    .InsertLines X + 20, "Unload Me"
    .InsertLines X + 21, "End Sub"
    End With




 event handler:

    Sub CommandButton1_Click()
    Dim rs As ADODB.Recordset
    Dim rst As ADODB.Recordset
    Dim j As Integer
    Dim ran As String
    j = 1
    Set rs = Rsfun
    Do While Not rs.EOF
    Dim check As MSForms.CheckBox
    Set check = UserForm1.Controls.Add("Forms.checkbox.1")
    check.Value = MyCheck & j.Value
    If check.Value = True Then
    ran = "A1"
    MsgBox (ran)
    range(ran).Value = rs.Fields(0)
    End If
    j = j + 1
    rs.MoveNext
    Loop
    Unload Me
    End Sub

Answer 1

Instead of check.Value = MyCheck & j.Value you need to

check.Value = UserForm1.Controls(MyCheck & j).Value

Another thing with:

Set check = UserForm1.Controls.Add("Forms.checkbox.1")

will add your cb to upper left corner and next one will cover previous. Maybe after adding you want to move it just a little bit like check.top = j * 15

Good luck.