Hi Montana,

1) Is it even possible to create a user-defined event that will fire every
time a checkbox--any checkbox--in the sheet is clicked?

2) Assuming that question 1 is possible, how would I make the event check
all the other checkboxes in the sheet? Is there a collection that would
contain all the controls in the sheet?

It's possible, using a class module, but personally, I tend to err on the
side of simplicity and have the 46 routines:

Private Sub CheckBox1_Click():HandleChecks 1:End Sub
Private Sub CheckBox2_Click():HandleChecks 2:End Sub
'etc.

'If any check boxes unticked, caption is "Tick All"
'If all check boxes ticked, change caption to "Untick All"
Private Sub HandleChecks(iCheckBox As Integer)

Dim i As Integer

If Me.Controls("CheckBox" & iCheckBox).Value = False Then
'This one was unticked, so no need to check the rest
btnTick.Caption = "Tick All"
Else
'This one was ticked, so check if they're all ticked
btnTick.Caption = "Untick All"
For i = 1 To 46
If Me.Controls("CheckBox" & i).Value = False Then
btnTick.Caption = "Tick All"
Exit For
End If
Next
End If

End Sub

FWIW, an easy way to create the 46 functions is to use Excel formulas:
="Private Sub CheckBox1"&ROW()&"_Click():..."

then copy it down to row 46, recalc and copy/paste the cells into the
procedure.


Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk