declaring an array of CheckBox's

[Didier Poskin]

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 declare:

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.

 

[Dave Ramage]

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 declare:

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.

1/09/2003

 

[Didier Poskin]

Dear you all whose patience is without limit,

The checkboxes I've already inserted on my sheet("MENU") do come from the
control toolbox toolbar
should I delete them?

I must humbly admit I 'm pretty new at this...

Tom,

Both of your test codes have no result at all !?

To my defense the checkboxes I've set are placed each in a cell in the
range(D2 -G15) that is 14 lines of 4 cells...

lost again, I'm really trying hard to grasp your meaning..

Regards,

Didier.

 

[Tom Ogilvy]

They are in ranges, they are over ranges. There is no connection from the
range to the checkbox - but the checkbox knows what cell it is located over.

I showed you how to determine that.

you can loop through the checkboxes and fine the one you want, or you can
process them all at once and use an array to build an association between
the checkboxes and the range.

You can write code to do this as intelligently and as efficiently as you
want, but since I don't know what you want to do, I can't tell you how to do
this. Yes, you can create an array that has a reference to each checkbox.

 

[Dave Ramage]

Didier,

This probably means that there is no checkbox from the
control toolbox on "Sheet1" that is called "CB_1_1" in
your workbook. Did you set it up correctly?

I'll PM you a demo workbook with this code included- that
should help you get your head around it...

Cheers,
Dave