Userform with checkbox and variables

texasalynn

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
I thought this would be something simple, but I'm stuck. Here is what I am trying:
Code: 
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?
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
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:

Code: 
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
 
Upvote 0
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.
 
Upvote 0
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 :)
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
Checkbox doesnt show checkmark when it should
Replies
2
Views
170
ipbr21054
I
R
  • Solved
Use checkboxes to convert formulas to values in multiple cells
Replies
2
Views
405
Rainmanne
R
H
  • Question Question
Strange CheckBox issue
Replies
2
Views
283
hazmat
H
I
  • Solved
Clear / Reset Checkbox on worksheet when userform has a submission
Replies
2
Views
248
ipbr21054
I
N
  • Solved
Frame scrollbar resets when clicking on/in frame
Replies
5
Views
356
Micron
M

Forum statistics

Threads
1,224,521
Messages
6,179,285
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top