Catching check/uncheck event accross multiple checkboxes

[Mac]

Hello,
in a sheet with a couple dozens of checkboxes I need to be able to catch the
event when each checkbox gets check or unchecked; probably the worst scenario
would be defining an event handler for each checkbox. I wonder if there is a
way for me to catch 'a global' check / uncheck event and only after that
decode which checkbox had sent that event....?

 

[OssieMac]

Hi Mac,

I think that best way to handle this is to have a command button called
Continue or Next otherwise you will never know when the user has finished
making the selections. The user might just make one or two selections or many
more selections. They might even change their mind and uncheck boxes that
they previously checked.

You can test each check box for true or false after the command button is
clicked.

 

[Dave Peterson]

If these checkboxes are on a worksheet, another option would be to use the
checkboxes from the Forms toolbar (not the Control Toolbox toolbar).

You could assign the same macro to each of the checkboxes.

Option Explicit
Sub Testme()
Dim CBX As CheckBox
Set CBX = ActiveSheet.CheckBoxes(Application.Caller)

If CBX.Value = xlOn Then
MsgBox "it's checked"
Else
MsgBox "it's not checked"
End If

'for instance...
MsgBox CBX.TopLeftCell.Address(0, 0) & vbLf & CBX.Name

End Sub

 

[Mac]

Having a button to serve as an 'orchestrator' is not a problem but I am
looking for a more elegant and promptly way of processing the clicks; the
scenario I envision is goes like this: the user clicks a checkbox ( checks or
unchecks it, that is not important at this moment), the checkbox broadcasts a
'they clicked me' event with additional data, my own handler consumes this
event and processes its data ( what is the id of the box, was it checked or
unchecked, ...) then pases it on. Is that feasible? But maybe this gets too
low-level ...

 

[Mac]

Joel, the last line in your code: Set but = ... Now it appears to me that
'but' is undefined ....

 

[Mac]

This is it, Michael! The truth is that I AM using the Forms type
checkboxes; so - if I iterate through Worksheets(x).Shapes Items and set each
items's OnAction to the desired routine, that should solve it. Is that a
correct way to do that?

 

[Dave Peterson]

I would use the Checkboxes collection--it's less complicated.

I added some stuff that you may not need.

The linkedcell is assigned to the cell that contains the checkbox. But it's
formatted as ";;;". This means that the value in the cell doesn't appear in the
worksheet--but is still visible in the formula bar.

Using a linked cell means that you could count the number of checkboxes that are
checked with something like:

=countif(a1:a10,true)

(Remove any of those things that you don't want--especially captions.)

Option Explicit
Sub testme()

Dim CBX As CheckBox
Dim wks As Worksheet

Set wks = Worksheets("Sheet1")

For Each CBX In wks.CheckBoxes
With CBX
.LinkedCell = .TopLeftCell.Address(external:=True)
.Caption = ""
.Name = "CBX_" & .TopLeftCell.Address(0, 0)
.OnAction = "'" & ThisWorkbook.Name & "'!dothework"
.TopLeftCell.NumberFormat = ";;;"
End With
Next CBX

End Sub
Sub DoTheWork()
Dim CBX As CheckBox
Set CBX = ActiveSheet.CheckBoxes(Application.Caller)

If CBX.Value = xlOn Then
MsgBox "it's checked"
Else
MsgBox "it's not checked"
End If

'for instance...
MsgBox CBX.TopLeftCell.Address(0, 0) & vbLf & CBX.Name
End Sub

ps.

Here's a routine I've posted before that creates the checkboxes and assigns the
macros...

Option Explicit
Sub testme()

Dim myCBX As CheckBox
Dim myCell As Range

With ActiveSheet
.CheckBoxes.Delete 'nice for testing
For Each myCell In .Range("B3:B10").Cells
With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
.LinkedCell = myCell.Address(external:=True)
.Caption = ""
.Name = "CBX_" & myCell.Address(0, 0)
.OnAction = "'" & ThisWorkbook.Name & "'!dothework"
End With
.NumberFormat = ";;;"
End With

Next myCell
End With
End Sub

Sub DoTheWork()
Dim myCBX As CheckBox
Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)

If myCBX = xlOn Then
'do something
Else
'do something else
End If

End Sub

ps. If you ever need a similar routine for optionbuttons from the Forms
toolbar, visit Debra Dalgleish's site:

http://contextures.com/xlForm01.html

 

[Mac]

Ok thank you guys for great insights, now I have got all the input that I
need.