Hello,

I am having a problem with an array of checkbox on my sheet("MENU").
this array reefers to quite a big amount of data that are to be included or not (according to checkbox's values)
in a report I am creating.

The problem is as follows

how can I access those checkboxes by row-column reference
since there are 15 lines by 4 columns and I don't want to write the code 60 times changing only the name of the referenced checkbox?

VBA allows me to decla

Dim xCheckBox (1 to 15, 1 to 4) as checkbox

but then I cannot enter " xCheckBox(1,1) " as the Name of the CheckBox : I get an error telling me it's not a valid name for the specified Object.

I've tried as well to reference the checboxes with like names:

x0101checkbox , x0102checkbox , x0103checkbox , x0104checkbox
x0201checkbox , x0202checkbox , x0203checkbox , x0204checkbox
x0301checkbox , x0302checkbox , x0303checkbox , etc

and then access them by declaring a string which in turns assume all the different checkbox's names
like

with i from 1 to 15 , j from 1 to 4
variableString = "x" & numLine(i) & numCol(j) & "checkbox" (where numLine(1) ="01" etc..)
check = sheets("MENU").variableString

but a get an invalid method for the object.

Must say I'm a bit stuck on this one !

Anyone an Idea?

Regards,

Didier.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.515 / Virus Database: 313 - Release Date: 1/09/2003

Didier,

This demo should get you sorted...

'six checkboxes on Sheet1, 2 rows * 3 columns
' names are CB_1_1 CB_1_2 CB_1_3
' CB_2_1 CB_2_2 CB_2_3
Sub test()
Dim CB_Array(1 To 2, 1 To 3) As MSForms.CheckBox
Dim r As Integer, c As Integer

'load array
For r = 1 To 2
For c = 1 To 3
Set CB_Array(r, c) = Sheets("Sheet1").OLEObjects
("CB_" & r & "_" & c).Object
Next c
Next r

'set all checkboxes to true
For r = 1 To 2
For c = 1 To 3
CB_Array(r, c).Value = True
Next c
Next r
End Sub

Cheers,
Dave.
-----Original Message-----
Hello,

I am having a problem with an array of checkbox on my
sheet("MENU").
this array reefers to quite a big amount of data that are
to be included or not (according to checkbox's values)
in a report I am creating.

The problem is as follows

how can I access those checkboxes by row-column reference
since there are 15 lines by 4 columns and I don't want to
write the code 60 times changing only the name of the
referenced checkbox?

VBA allows me to decla

Dim xCheckBox (1 to 15, 1 to 4) as checkbox

but then I cannot enter " xCheckBox(1,1) " as the Name of
the CheckBox : I get an error telling me it's not a valid
name for the specified Object.

I've tried as well to reference the checboxes with like
names:

x0101checkbox , x0102checkbox , x0103checkbox ,
x0104checkbox
x0201checkbox , x0202checkbox , x0203checkbox ,
x0204checkbox
x0301checkbox , x0302checkbox , x0303checkbox , etc

and then access them by declaring a string which in turns
assume all the different checkbox's names
like

with i from 1 to 15 , j from 1 to 4
variableString = "x" & numLine(i) & numCol(j)
& "checkbox" (where numLine(1) ="01" etc..)
check = sheets("MENU").variableString

but a get an invalid method for the object.

Must say I'm a bit stuck on this one !

Anyone an Idea?

Regards,

Didier.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.515 / Virus Database: 313 - Release Date:
1/09/2003