Userform with checkbox and variables

[texasalynn]

I thought this would be something simple, but I'm stuck. Here is what I am trying:

Private Sub UserForm_Initialize()
CBox = "CheckBox"
For i = 1 To 10
Set CBox = CBox & i
If Cells(i, 3).Value <> "" Then
CBox.Caption = Cells(i, 3).Value
Else
CBox.Visible = False
End If
Next

End Sub

I wanted to assign the checkbox(x) as a variable and then it would loop through the range to see if there is a value. If the range is empty then the checkbox wouldn't show on the userform. I'm I missing something?

 

[Von Pookie]

Looping through controls can be a little tricky. Looks like you're trying to loop through them with the control name as a string variable, which I've found VBA generally doesn't like. You need to make sure it's a control.

This seems to be working for me:

Private Sub UserForm_Initialize()
Dim CBox As Control, i As Long

For i = 1 To 10
    Set CBox = Me.Controls("CheckBox" & i)
    If Cells(i, 3) <> "" Then
        With CBox
            .Caption = Cells(i, 3)
            .Visible = True
        End With
    Else
        CBox.Visible = False
    End If
Next i
        
End Sub

 

[texasalynn]

great thanks - I think I was also hurting myself because I only had 8 checkboxes created on the form. Once I created the 10 checkboxes you code worked great.

 

[Von Pookie]

Yeah, that would cause an error as well. I did that myself when I was testing it. Took me a second to realize what I did

 

[Sandcastor]

Wow, 6 years later and this is still a great tip! Super-big thanks to Kristy von Pookie, I have been scouring for days for a way to loop checkboxes.

I wanted the "i" variable (For i = 1 To 12) to help with a for-loop, parse a dozen checkboxes on the form for true, display as a text in an input box, and help store responses in an array n(i).

I had been trying the same approach as texasalynn, obviously to no effect.

Finally the Missing Piece!!

Thanks!

M