Trap a checkbox click

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" <[email protected]> wrote in message
news:e%[email protected]...
> 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
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:e%[email protected]...
>> 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
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:e%[email protected]...
>> 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
>>
>
>

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

> 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
> news:%[email protected]...
> > 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" <[email protected]> wrote in message
> > news:e%[email protected]...
> >> 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
> >>
> >
> >
>
>
>

Greg
Thanks a bunch. I'll give that a try. Otto
"Greg Wilson" <[email protected]> wrote in message
news:[email protected]...
> '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" wrote:
>
>> 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
>> news:%[email protected]...
>> > 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" <[email protected]> wrote in message
>> > news:e%[email protected]...
>> >> 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
>> >>
>> >
>> >
>>
>>
>>

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
"Greg Wilson" <[email protected]> wrote in message
news:[email protected]...
> '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" wrote:
>
>> 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
>> news:%[email protected]...
>> > 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" <[email protected]> wrote in message
>> > news:e%[email protected]...
>> >> 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
>> >>
>> >
>> >
>>
>>
>>