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