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?

CheckBox question
 

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?

CheckBox question
 

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?

CheckBox question
 

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?

CheckBox question
 

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?

CheckBox question
 

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?

CheckBox question
 

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" wrote in message
...
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?

CheckBox question
 

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" wrote in message
...
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?

CheckBox question
 

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" wrote in message
...
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" wrote in message
...
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?