Checking all checkboxes

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?

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.

hows this code

for i = 0 to ubound(arrChkBoxes)
arrChkBoxes(i).value=1
next i

Personally, i'm not sure how to do it if they aren't in an array.

-pcweirdo.

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
End If
Next ctl

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

Can I use this idea to change the value of them? If so, how do I get around the different names of the checkboxes?

To make controls into an array, give them all the same name and give the index property of the control a number...

i.e.

CheckBox(0)
CheckBox(1)
CheckBox(2)
CheckBox(3)

Instead of

CheckBox0
CheckBox1
CheckBox2
CheckBox3

Just makes life easier...

For i = 0 to 20
CheckBox(i).Value = 1
Next

Something like that.

Cheers folks! That's all working now.

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