CheckBox question

[Guest]

I have 20 checkboxes, and 20 objects. I want to load the user form with the
checkboxes, and for each checkbox selected, I want to select that object. So
CheckBox1 corresponds to Object1 and so forth to 20. Any ideas?

 

[Guest]

You have 20 checkboxes on a Userform and you want to select 20 corresponding
objects on a worksheet if the checkbox is checked. What is the correlation
between the names of objects and the name of the checkboxes. do they both
end in the same 2 digit numbers? Is the root name the same for all the
objects? Waht is it?

Are you sure you can't just use a list of names and work with the objects
rather than selecting them?

If the checkboxes are not on a userform - they are on a worksheet, are the
from the control toolbox toolbar or the forms toolbar?

 

[Guest]

Checkboxes are on a userform. And Object1 goes with CheckBox 1, Object2 goes,
with CheckBox2, and so forth to 20.

 

[Guest]

this worked for me:

Private Sub CommandButton1_Click()
Dim cbox As MSForms.CheckBox
Dim v() As Variant
Dim i As Long
i = 0
ReDim v(0 To 0)
For Each ctrl In Me.Controls
If TypeOf ctrl Is MSForms.CheckBox Then
Set cbox = ctrl
If cbox.Value = True Then
ReDim Preserve v(0 To i)
If Len(ctrl.Name) = 9 Then
v(i) = "Object" & Right(ctrl.Name, 1)
Else
v(i) = "Object" & Right(ctrl.Name, 2)
End If
i = i + 1
End If
End If
Next
If i = 1 Then
ActiveSheet.Shapes(v(0)).Select
Else
ActiveSheet.Shapes.Range(v).Select
End If

End Sub

 

[Guest]

Tom, thanks. This makes sense and should work, but I'm still having problems.
I am showing the form in one sub, and then have the Command Button Sub like
you have below for the OK button on the form. When I step through it, it gets
to the part "If TypeOf Ctrl" and then jumps down to the End If. I assume the
Me.Controls, the Me is the Userform name, right? So I replaced that with my
userform name. Looks like it's not recognizing the controls as checkboxes.

 

[Guest]

Still out there Tom?

Tom, thanks. This makes sense and should work, but I'm still having problems.
I am showing the form in one sub, and then have the Command Button Sub like
you have below for the OK button on the form. When I step through it, it gets
to the part "If TypeOf Ctrl" and then jumps down to the End If. I assume the
Me.Controls, the Me is the Userform name, right? So I replaced that with my
userform name. Looks like it's not recognizing the controls as checkboxes.

 

[Tom Ogilvy]

I can't say anything about why you are having trouble stepping through the
code. I never step through code, so it isn't something I can advise you on.

Me is a reference to the userform owning the module when used in a userform
module. It is a reference to the sheet that contains the code when used in
a sheet module - so replacing it with the name of the userform should work
but shouldn't be necessary. If you renamed the userform, "ME" would still
work, but Userform1 would not.

If you took off the MSForms from Checkbox, then that would be another error.
MSforms.Checkbox is the type of a control toolbox checkbox on a userform.

As I said, it worked fine for me (xl2003), so don't know what to tell you.


You can add the declaration

Dim ctrl as MSForms.Control


--------
Some further test code that worked: (xl97)

Private Sub CommandButton1_Click()
Dim ctrl As MSForms.Control
Dim cbox As MSForms.CheckBox
For Each ctrl In Me.Controls
If TypeOf ctrl Is MSForms.CheckBox Then
Set cbox = ctrl
msgbox cbox.Name
End If
Next
End Sub

 

[Guest]

Stepping through it just tells me exactly where it is getting hung up on.
Very helpful in pinpointing the problem. It doesnt seem to recognize MSForms
either. At least when I type it lower case, it doesn't automatically
capitalize it like it is recognized.

 

[Tom Ogilvy]

I know what stepping through code is. Also stepping through doesn't always
behave as the actual code will.

If it doesn't recognize it, then you don't have a reference to the MSForms
library, but you must if you have a userform.