Excel 2002, WinXP
I posted this problem originally on 27 June. Tom Ogilvy responded and
helped me a great deal. He directed me to John Walkenbach's site:
http://j-walk.com/ss/excel/tips/tip44.htm
Handle Multiple UserForm Buttons With One Subroutine
and suggested that I could adapt John's method to my problem. My problem is
very similar to that addressed in John's article except that I have many
checkboxes in a sheet rather than many buttons in a UserForm.
My original post thread is no longer getting any responses, so I am starting
a new thread with this post. Any help anyone can provide would be much
appreciated.
From the code provided in John's article and the help I received from Tom, I
have the following:

In a class module:
Public WithEvents CheckBoxGroup As MSFORMS.CheckBox
Private Sub CheckBoxGroup_Click()
MsgBox "Hello from " & CheckBoxGroup.Name
End Sub

In a regular module:
Sub SetupCBGroup()
Dim CheckBoxCount As Long
Dim ctl As Control
' Create the CheckBox objects
CheckBoxCount = 0
For Each ctl In ActiveSheet.Controls 'Error on this line
If TypeName(ctl) = "CheckBox" Then
CheckBoxCount = CheckBoxCount + 1
ReDim Preserve CheckBoxes(1 To CheckBoxCount)
Set CheckBoxes(CheckBoxCount).CheckBoxGroup = ctl
End If
Next ctl
End Sub

When I run the SetupCBGroup macro I get:
Object doesn't support this property or method." in the "For Each ctl..."
line.

My objective is:
Know that a checkbox has been clicked on.
Know what checkbox has been clicked on.
Know the state of that checkbox (TRUE or FALSE)

Thanks for your help. Otto

Hi Otto:

I think you need to use:

Dim ctl As OLEObject
....................................
For Each ctl In ActiveSheet.OLEObjects
If TypeOf ctl.Object Is msforms.CheckBox Then

(Haven't looked at the rest of your code in detail.)

Regards,

Vasant

"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
I posted this problem originally on 27 June. Tom Ogilvy responded and
helped me a great deal. He directed me to John Walkenbach's site:
http://j-walk.com/ss/excel/tips/tip44.htm
Handle Multiple UserForm Buttons With One Subroutine
and suggested that I could adapt John's method to my problem. My problem
is very similar to that addressed in John's article except that I have
many checkboxes in a sheet rather than many buttons in a UserForm.
My original post thread is no longer getting any responses, so I am
starting a new thread with this post. Any help anyone can provide would
be much appreciated.
From the code provided in John's article and the help I received from Tom,
I have the following:

In a class module:
Public WithEvents CheckBoxGroup As MSFORMS.CheckBox
Private Sub CheckBoxGroup_Click()
MsgBox "Hello from " & CheckBoxGroup.Name
End Sub

In a regular module:
Sub SetupCBGroup()
Dim CheckBoxCount As Long
Dim ctl As Control
' Create the CheckBox objects
CheckBoxCount = 0
For Each ctl In ActiveSheet.Controls 'Error on this line
If TypeName(ctl) = "CheckBox" Then
CheckBoxCount = CheckBoxCount + 1
ReDim Preserve CheckBoxes(1 To CheckBoxCount)
Set CheckBoxes(CheckBoxCount).CheckBoxGroup = ctl
End If
Next ctl
End Sub

When I run the SetupCBGroup macro I get:
Object doesn't support this property or method." in the "For Each ctl..."
line.

My objective is:
Know that a checkbox has been clicked on.
Know what checkbox has been clicked on.
Know the state of that checkbox (TRUE or FALSE)

Thanks for your help. Otto

Thanks Vasant, I'll try that. Otto
"Vasant Nanavati" <vasantn AT aol DOT com wrote in message
...
Hi Otto:

I think you need to use:

Dim ctl As OLEObject
...................................
For Each ctl In ActiveSheet.OLEObjects
If TypeOf ctl.Object Is msforms.CheckBox Then

(Haven't looked at the rest of your code in detail.)

Regards,

Vasant

"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
I posted this problem originally on 27 June. Tom Ogilvy responded and
helped me a great deal. He directed me to John Walkenbach's site:
http://j-walk.com/ss/excel/tips/tip44.htm
Handle Multiple UserForm Buttons With One Subroutine
and suggested that I could adapt John's method to my problem. My problem
is very similar to that addressed in John's article except that I have
many checkboxes in a sheet rather than many buttons in a UserForm.
My original post thread is no longer getting any responses, so I am
starting a new thread with this post. Any help anyone can provide would
be much appreciated.
From the code provided in John's article and the help I received from
Tom, I have the following:

In a class module:
Public WithEvents CheckBoxGroup As MSFORMS.CheckBox
Private Sub CheckBoxGroup_Click()
MsgBox "Hello from " & CheckBoxGroup.Name
End Sub

In a regular module:
Sub SetupCBGroup()
Dim CheckBoxCount As Long
Dim ctl As Control
' Create the CheckBox objects
CheckBoxCount = 0
For Each ctl In ActiveSheet.Controls 'Error on this line
If TypeName(ctl) = "CheckBox" Then
CheckBoxCount = CheckBoxCount + 1
ReDim Preserve CheckBoxes(1 To CheckBoxCount)
Set CheckBoxes(CheckBoxCount).CheckBoxGroup = ctl
End If
Next ctl
End Sub

When I run the SetupCBGroup macro I get:
Object doesn't support this property or method." in the "For Each ctl..."
line.

My objective is:
Know that a checkbox has been clicked on.
Know what checkbox has been clicked on.
Know the state of that checkbox (TRUE or FALSE)

Thanks for your help. Otto

Vasant
That helped a lot, but I now have a Type Mismatch error in the line:
Set CheckBoxes(CheckBoxCount).CheckBoxGroup = ctl
I got that code from John Walkenbach's site. I changed the "buttons" to
"checkboxes", that's all.
I remarked out that line and ran the SetupCBGroup macro to setup the
checkbox group. It ran fine but a subsequent click on one of the checkboxes
in the sheet did not produce a "Hello from " & CheckBoxGroup.Name message.
I appreciate any help you can give me. Otto

"Vasant Nanavati" <vasantn AT aol DOT com wrote in message
...
Hi Otto:

I think you need to use:

Dim ctl As OLEObject
...................................
For Each ctl In ActiveSheet.OLEObjects
If TypeOf ctl.Object Is msforms.CheckBox Then

(Haven't looked at the rest of your code in detail.)

Regards,

Vasant

"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
I posted this problem originally on 27 June. Tom Ogilvy responded and
helped me a great deal. He directed me to John Walkenbach's site:
http://j-walk.com/ss/excel/tips/tip44.htm
Handle Multiple UserForm Buttons With One Subroutine
and suggested that I could adapt John's method to my problem. My problem
is very similar to that addressed in John's article except that I have
many checkboxes in a sheet rather than many buttons in a UserForm.
My original post thread is no longer getting any responses, so I am
starting a new thread with this post. Any help anyone can provide would
be much appreciated.
From the code provided in John's article and the help I received from
Tom, I have the following:

In a class module:
Public WithEvents CheckBoxGroup As MSFORMS.CheckBox
Private Sub CheckBoxGroup_Click()
MsgBox "Hello from " & CheckBoxGroup.Name
End Sub

In a regular module:
Sub SetupCBGroup()
Dim CheckBoxCount As Long
Dim ctl As Control
' Create the CheckBox objects
CheckBoxCount = 0
For Each ctl In ActiveSheet.Controls 'Error on this line
If TypeName(ctl) = "CheckBox" Then
CheckBoxCount = CheckBoxCount + 1
ReDim Preserve CheckBoxes(1 To CheckBoxCount)
Set CheckBoxes(CheckBoxCount).CheckBoxGroup = ctl
End If
Next ctl
End Sub

When I run the SetupCBGroup macro I get:
Object doesn't support this property or method." in the "For Each ctl..."
line.

My objective is:
Know that a checkbox has been clicked on.
Know what checkbox has been clicked on.
Know the state of that checkbox (TRUE or FALSE)

Thanks for your help. Otto