Trap a checkbox click

[Otto Moehrbach]

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 Nanavati]

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]

Thanks Vasant, I'll try that. 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]

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

 

[Guest]

'Class module code
Public WithEvents CheckBoxGroup As MSForms.CheckBox
Private Sub CheckBoxGroup_Click()
MsgBox "Hello from " & CheckBoxGroup.Name & _
vbCr & "My value is " & CheckBoxGroup.Value
End Sub

'Standard module code
Dim CheckBoxes() As New Class1
Sub SetupCBGroup()
Dim CheckBoxCount As Long
Dim OleObj As OLEObject
CheckBoxCount = 0
For Each OleObj In ActiveSheet.OLEObjects
If TypeOf OleObj.Object Is MSForms.CheckBox Then
CheckBoxCount = CheckBoxCount + 1
ReDim Preserve CheckBoxes(1 To CheckBoxCount)
Set CheckBoxes(CheckBoxCount).CheckBoxGroup = OleObj.Object
End If
Next OleObj
End Sub

Regards,
Greg

 

[Otto Moehrbach]

Greg
Thanks a bunch. I'll give that a try. Otto

'Class module code
Public WithEvents CheckBoxGroup As MSForms.CheckBox
Private Sub CheckBoxGroup_Click()
MsgBox "Hello from " & CheckBoxGroup.Name & _
vbCr & "My value is " & CheckBoxGroup.Value
End Sub

'Standard module code
Dim CheckBoxes() As New Class1
Sub SetupCBGroup()
Dim CheckBoxCount As Long
Dim OleObj As OLEObject
CheckBoxCount = 0
For Each OleObj In ActiveSheet.OLEObjects
If TypeOf OleObj.Object Is MSForms.CheckBox Then
CheckBoxCount = CheckBoxCount + 1
ReDim Preserve CheckBoxes(1 To CheckBoxCount)
Set CheckBoxes(CheckBoxCount).CheckBoxGroup = OleObj.Object
End If
Next OleObj
End Sub

Regards,
Greg

 

[Otto Moehrbach]

Greg
That works perfectly. Thanks for taking the time to give me the
complete code. I do a lot of coding but this code was something new for me.
Thanks again. Otto