CheckBox question

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?

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?

--
Regards,
Tom Ogilvy


"Mike" wrote:

> 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?

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

"Tom Ogilvy" wrote:

> 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?
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Mike" wrote:
>
> > 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?

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

--
Regards,
Tom Ogilvy


"Mike" wrote:

> Checkboxes are on a userform. And Object1 goes with CheckBox 1, Object2 goes,
> with CheckBox2, and so forth to 20.
>
> "Tom Ogilvy" wrote:
>
> > 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?
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Mike" wrote:
> >
> > > 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?

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" wrote:

> 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
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Mike" wrote:
>
> > Checkboxes are on a userform. And Object1 goes with CheckBox 1, Object2 goes,
> > with CheckBox2, and so forth to 20.
> >
> > "Tom Ogilvy" wrote:
> >
> > > 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?
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "Mike" wrote:
> > >
> > > > 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?

Still out there Tom?

"Mike" wrote:

> 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" wrote:
>
> > 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
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Mike" wrote:
> >
> > > Checkboxes are on a userform. And Object1 goes with CheckBox 1, Object2 goes,
> > > with CheckBox2, and so forth to 20.
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > 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?
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > > "Mike" wrote:
> > > >
> > > > > 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?

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

--
Regards,
Tom Ogilvy



"Mike" <[email protected]> wrote in message
news:[email protected]...
> Still out there Tom?
>
> "Mike" wrote:
>
> > 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" wrote:
> >
> > > 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
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "Mike" wrote:
> > >
> > > > Checkboxes are on a userform. And Object1 goes with CheckBox 1,
Object2 goes,
> > > > with CheckBox2, and so forth to 20.
> > > >
> > > > "Tom Ogilvy" wrote:
> > > >
> > > > > 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?
> > > > >
> > > > > --
> > > > > Regards,
> > > > > Tom Ogilvy
> > > > >
> > > > >
> > > > > "Mike" wrote:
> > > > >
> > > > > > 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?

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" wrote:

> 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
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Mike" <[email protected]> wrote in message
> news:[email protected]...
> > Still out there Tom?
> >
> > "Mike" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > > "Mike" wrote:
> > > >
> > > > > Checkboxes are on a userform. And Object1 goes with CheckBox 1,
> Object2 goes,
> > > > > with CheckBox2, and so forth to 20.
> > > > >
> > > > > "Tom Ogilvy" wrote:
> > > > >
> > > > > > 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?
> > > > > >
> > > > > > --
> > > > > > Regards,
> > > > > > Tom Ogilvy
> > > > > >
> > > > > >
> > > > > > "Mike" wrote:
> > > > > >
> > > > > > > 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?
>
>
>

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.

--
Regards,
Tom Ogilvy
"Mike" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> > 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
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "Mike" <[email protected]> wrote in message
> > news:[email protected]...
> > > Still out there Tom?
> > >
> > > "Mike" wrote:
> > >
> > > > 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" wrote:
> > > >
> > > > > 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
> > > > >
> > > > > --
> > > > > Regards,
> > > > > Tom Ogilvy
> > > > >
> > > > >
> > > > > "Mike" wrote:
> > > > >
> > > > > > Checkboxes are on a userform. And Object1 goes with CheckBox 1,
> > Object2 goes,
> > > > > > with CheckBox2, and so forth to 20.
> > > > > >
> > > > > > "Tom Ogilvy" wrote:
> > > > > >
> > > > > > > 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?
> > > > > > >
> > > > > > > --
> > > > > > > Regards,
> > > > > > > Tom Ogilvy
> > > > > > >
> > > > > > >
> > > > > > > "Mike" wrote:
> > > > > > >
> > > > > > > > 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?
> >
> >
> >