Click Checkbox Event

[Montana DOJ Help Desk]

Excel 2000

I have an Excel sheet with 46 checkboxes that are grouped into 10
categories. With each category there is a command button that can be used
to check all the checkboxes in the category, or to clear them all.
Basically, it all works, but I have noticed one small problem that I would
like to solve just as a learning exercise (it's not really something that
must be fixed).

Say that 5 out of 6 checkboxes in a category are checked, and that the
command button for the category reads "Check All". If the last checkbox is
checked manually, all the boxes in the category will be checked, but the
command button will still read "Check All" when it should read "Clear All".
This condition can easily be fixed by clicking the command button, which
will check all the boxes and change the caption of the button to "Clear
All".

Conceptually, I can see how this small problem could be fixed, but I don't
know enough about Excel VBA to know if my idea is feasible, and I don't know
the commands to get there. However, I've recently been working a lot with
Word VBA, so hopefully there will be some VBA similarities between the two
applications.

I'm not too keen on the idea of adding 46 routines (one for each checkbox)
to my code, so my idea is to create a user-defined event that will run every
time a checkbox is clicked. For lack of a better term, let's call this the
"click checkbox event". When any checkbox in the sheet is clicked, the
click checkbox event would run through all the checkboxes in the active
sheet and find all the other checkboxes that are in the same category as the
checkbox that was clicked (I have them all named in such a way that would
make that possible). As it found other checkboxes that are in the same
category, it would look at how each is set. At the end of the process, if
all the checkboxes in the category are set the same way, the caption of the
command button for that category would be set accordingly.

So my questions are:

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?

-- Tom

State of Montana
Department of Justice Help Desk

"Making the world a safer place."

 

[Stephen Bullen]

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

 

[Thomas M]

I think I'll give it a try using a class module, since I've never used
one of those before--it'll be a good learning experience.

Can someone give me a pointer to a good information source on class
modules (I haven't found much in the VBA Help), or maybe post the code to
a simple class module and explain what each element does?

--Tom

 

[Stephen Bullen]

Hi Thomas,

Can someone give me a pointer to a good information source on class
modules (I haven't found much in the VBA Help), or maybe post the code to
a simple class module and explain what each element does?

John Walkenbach has a simple example at:

http://j-walk.com/ss/excel/tips/tip44.htm

though I prefer to use a Collection object rather than an array to store
each class instance.

Regards

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

 

[Montana DOJ Help Desk]

Thanks. I'll take a look at it and see what I can come up with.

-- Tom

State of Montana
Department of Justice Help Desk

"Making the world a safer place."