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

Trap a checkbox click
 

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

Trap a checkbox click
 

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

Trap a checkbox click
 

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

Trap a checkbox click
 

Greg
Thanks a bunch. I'll give that a try. Otto
"Greg Wilson" wrote in message
...
'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
...
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

Trap a checkbox click
 

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