Excel 2000

This isn't a pressing issue, but I'm nonetheless curious if Excel can do
what I want in a way that is more elegant that my current solution.

I have a worksheet that has a couple of dozen checkboxes that are broken
into categories. Next to each category is a command button that functions
as a toggle and can be used to check all the boxes in the category, or clear
them all. By default, the command button caption is "Check Category" and it
will check all the boxes in the category. If all the boxes are currently
checked, then the command button reads "Clear Category" and it will clear
all the boxes in the category.

However, if each of the boxes in a category are checked manually, it is
possible to end up with all the boxes checked, and the command button will
still say "Check Category" when it should say "Clear Category." This is a
trivial issue, but I got curious as to whether or not I could make the
caption on the command button change automatically when all the checkboxes
are checked manually. The only solution that I could come up with is to
make each checkbox run a routine that would check the value of the other
checkboxes in the category, and would set the caption on the command button
accordingly. Here's the code:

Private Sub SendOverdueLetters_Click()
CheckOverdueLettersTasks
End Sub

Private Sub ValidationCertDoc_Click()
CheckOverdueLettersTasks
End Sub

Private Sub CopyOfRecords_Click()
CheckOverdueLettersTasks
End Sub

Private Sub RemoveRecordsReminder_Click()
CheckOverdueLettersTasks
End Sub

Sub CheckOverdueLettersTasks()
Counter = 0
If SendOverdueLetters.Value = True Then Counter = Counter + 1
If ValidationCertDoc.Value = True Then Counter = Counter + 1
If CopyOfRecords.Value = True Then Counter = Counter + 1
If RemoveRecordsReminder.Value = True Then Counter = Counter + 1
If Counter = 4 Then
cmdToggleOverdueLetters.Caption = "Clear Category"
Else
cmdToggleOverdueLetters.Caption = "Check Category"
End If
End Sub

This does work, but it seems clumsy to me. I'd have to add a couple of
dozen routines, and each would need to be slightly different because of the
control names and the number of checkboxes in each category.

Is there a way to group the checkboxes together so that clicking any one of
the checkboxes runs the above CheckOverdueLettersTasks() routine? Better
yet, is there a way to make all checkboxes run a routine that will detect
the category of the checkbox, find the other checkboxes in the same
category, read in their control names and values, then run a routine to set
the command button caption?

I realize this is a very minor thing, but it's just something that I got
curious about, and now I can't stop fiddlin' with it! Thanks for any help
you can offer.


-- Tom

MT DOJ Help Desk

Making the world a safer place.