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 a

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."