Trap a checkbox click

[Otto Moehrbach]

Excel 2002, WinXP
An OP I am helping has dozens of Control Toolbox checkboxes on each of
several sheets. The idea is for a user to click on the individual checkbox
in response to a question.
The OP has a Private Sub CheckBoxX_Click() macro for each checkbox with some
12 lines of code to take specific actions if that specific checkbox is
clicked True or False. That seems to me to be an awkward way to execute
code for each checkbox.

Each checkbox is linked to a neighboring cell so that the user can see the
TRUE or FALSE in response to his click.. I thought I could pick up on that
Worksheet_Change as an event but I found that link doesn't trigger an event.

My question is this:
Is there a way to detect all of the following?
A checkbox has been clicked on.
Which checkbox has been clicked on?
What is the current state of that one checkbox?

Thanks for your help. Otto

 

[Tom Ogilvy]

This technique works equally as well for checkboxes on worksheets as it does
for commandbuttons on userforms;

http://j-walk.com/ss/excel/tips/tip44.htm
John Walkenbach's site
Handle Multiple UserForm Buttons With One Subroutine

 

[Otto Moehrbach]

Thanks Tom. I'll look it up and see what I can do. Otto

 

[Otto Moehrbach]

Tom
I need some more help with this. Using John's example for multiple
CommandButtons in a UserForm as you said, I have the following:
In a Class module I have:

Public WithEvents CheckBoxGroup As CheckBox

Private Sub CheckBoxGroup_Click()
MsgBox "Hello from " & CheckBoxGroup.Name
End Sub


In a regular module I have:
Option Explicit
Dim CheckBoxes() As New Class1

Sub SetupCBGroup() 'John's example was ShowDialog
Dim CheckBoxCount As Long
Dim ctl As Control
' Create the CheckBox objects
CheckBoxCount = 0
For Each ctl In ActiveSheet.Controls
If TypeName(ctl) = "CheckBox" Then
CheckBoxCount = CheckBoxCount + 1
ReDim Preserve CheckBoxes(1 To CheckBoxCount)
Set CheckBoxes(CheckBoxCount).CheckBoxGroup = ctl
End If
Next ctl
'John's example had a ShowUF here
End Sub

I understand that the above SetupCBGroup macro has to run in order to setup
the CheckBoxes array. I could use a Sheet_Activate event to run this macro.
But for now I tried to run that macro manually to setup the array and got
the following error on the first statement ("Public WithEvents CheckBoxGroup
As CheckBox)in the Class module:
Compile error:

Object does not source automation events


Tom, I'm on thin ice with this because all this is new to me. I appreciate
your help. Otto

 

[Tom Ogilvy]

Public WithEvents CheckBoxGroup As MSFORMS.CheckBox

There is a checkbox object in Excel that is not the MSforms (ActiveX)
checkbox - so you need to be specific.

 

[Otto Moehrbach]

Thanks Tom. I'll try it with that change. Otto

Public WithEvents CheckBoxGroup As MSFORMS.CheckBox

There is a checkbox object in Excel that is not the MSforms (ActiveX)
checkbox - so you need to be specific.

 

[Otto Moehrbach]

Tom
I got another error. It was "Object doesn't support this property or
method." and was in the "For Each ctl..." line in the following macro:
Sub SetupCBGroup()
Dim CheckBoxCount As Long
Dim ctl As Control
' Create the CheckBox objects
CheckBoxCount = 0
For Each ctl In ActiveSheet.Controls 'Error
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

Thanks for your help, Tom Otto