I've got a worksheet with 30 odd checkboxes and I need to be able to switch all of them on and all of them off again by code without calling each of the names of the checkboxes. How can I do this? I've renamed them all so they are now called CheckboxP1..P9 and then CheckboxC1...C13 etc. I dunno if this will help or hinder. This needs to be done within a code I've got for an option box. Any thoughts?
09-11-2003, 05:14 AM
The best thing to do is have all of them in a control array and have a loop to loop through all of the checkboxes checking them.
09-11-2003, 05:24 AM
hows this code
for i = 0 to ubound(arrChkBoxes)
Personally, i'm not sure how to do it if they aren't in an array.
09-11-2003, 05:27 AM
May be this helps...
try the TypeOf Statement......
Dim ctl As Contol
For Each ctl In Form.Controls
If TypeOf ctl Is CheckBox Then
'Do the Checking Or Unchecking
This may be the way forwards, however - how do you make the checkboxes into arrays? And once they are in an array how do you use the name to change the value of the checkbox (being ticked or unticked)? In other parts of my sheet I've used the following formula to check if a box is ticked:
If CheckBoxD9.Value = True Then
Can I use this idea to change the value of them? If so, how do I get around the different names of the checkboxes?
09-11-2003, 10:44 AM
To make controls into an array, give them all the same name and give the index property of the control a number...
Just makes life easier...
For i = 0 to 20
CheckBox(i).Value = 1
Something like that.
Cheers folks! That's all working now.
09-12-2003, 02:32 PM
Incase you check back in this thread... here's a TIP.
Create your first control, then Copy and Paste it on your Form.
VB will ask you if you want to make a control array. Say yes and VB will automatically assign new index numbers...
You can create an array of 100+ controls in a matter of seconds this way. e.g. If you select a whole row of controls (index's 0-9) and copy and paste them, VB will assign the new controls index's 10-19