Making All Checkboxes Run the Same Code
 

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.

Making All Checkboxes Run the Same Code
 

Unfortunately, Excel VBA does not support control arrays. It's a huge bummer, especially since Excel is such a natural array medium

But there are some goofy, yet slightly more elegant ways to handle your situation. You can't create custom properties for a checkbox control, but you can embed the category type in the name of the control and use that to group them programmatically. Of course it's best if you use your category designation as a prefix or suffix, so you don't have to search the whole name for it

In iterating over such a group of controls you can either just check the name, like

Dim ctrl as Objec
Dim b as Boolea

'It's easier to assume they're all checke
b = Tru
For each ctrl in ActiveSheet.OLEObject
'If this is a category one checkbox and it's value is False, we're outa her
If Left$(ctrl.Name, 3) = PREFIX_CATEGORY_ONE and Not ctrl.Value The
b = Fals
Exit Fo
End I
Next ctr

If b The
cmdButton.Caption = "Clear Category One
Els
cmdButton.Caption = "Check Category One
End I

The main advantage of this code over yours is that you can add controls to the category without changing the code. Other than that it's still a little clunky

Using the OLEObjects collection can be a great way to get around controls in a more logical way. Be careful if you're using Excel '97 (and maybe 2000?) - if you refer to Worksheet.OLEObjects("controlname"), you must also remember to set the version of the control name that resides in the Define Name box in the Excel toolbar. Excel XP sets this automatically when you set the Name property of the control, but '97 doesn't. Yes, there are two versions of the control name

Good Luck

James

Making All Checkboxes Run the Same Code
 

I was just using the OLEObjects collection for a similar consolidation of code and realized that many of the underlying object's properties are not directly available through the OLEObject interface, but they are available through the .Object property of the OLEObject object. .Value is one of those properties.

-James

Making All Checkboxes Run the Same Code
 

Hello Tom,

Do you have any more concerns on this problem? If there is any we can do,
please feel free to post here. Thanks very much.

Best regards,
Yanhong Huang
Microsoft Community Support

Get Secure! ¨C www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

Making All Checkboxes Run the Same Code
 

This is the first chance I've had in the last week or so to check back in on
this thread. I've not yet had the chance to try James' solution, and
probably won't be able to try it for another week or two. At this time, I
have no further questions on this issue. If I have any other questions I
will post them accordingly.

-- Tom

MT DOJ Help Desk

Making the world a safer place.
"Yan-Hong Huang[MSFT]" wrote in message
...
Hello Tom,

Do you have any more concerns on this problem? If there is any we can do,
please feel free to post here. Thanks very much.

Best regards,
Yanhong Huang
Microsoft Community Support

Get Secure! ¨C www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no
rights.

Making All Checkboxes Run the Same Code
 

Hi Tom,

That is OK. :) If there are any more questions, please feel free to post in
the group. We are here to support you at your convenience.

Thanks for participating the community.

Best regards,
Yanhong Huang
Microsoft Community Support

Get Secure! ¨C www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.