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

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" a écrit dans le message de
...
"as checkbox" refers to a checkbox from the forms toolbar. Are your
checkboxes from the Forms toolbar or the control toolbox toolbar? It
makes
a big difference in how you can refer to them.

From the forms toolbar:

Sub tester10()
Dim chkbx As CheckBox
For Each chkbx In ActiveSheet.CheckBoxes
Debug.Print chkbx.Name & " - " & chkbx.TopLeftCell.Address
Next

End Sub

From the control toolbox toolbar

Sub Tester11()
Dim obj As OLEObject
Dim chkbx As MSForms.CheckBox
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.CheckBox Then
Set chkbx = obj.Object
Debug.Print obj.Name & " - " & chkbx.Name _
& " - "; obj.TopLeftCell.Address
End If
Next
End Sub

in xl2000 and later, the OleObject name, Shape Name and Control Name are
coordinated, but not true in xl97.

--
Regards,
Tom Ogilvy


" wrote in 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




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

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.

--
Regards,
Tom Ogilvy

Didier Poskin wrote in message
...
Thank you Tom,

they're all in the immediate window in VBE,
can I adress the checkboxes by the cells they are located in?
or can I use an array of them ? or any another method?

Regards,

Didier

"Tom Ogilvy" a écrit dans le message de
...
Did you look in the immediate window in the VBE - that is where the
results
are written - less burdensome than OK'ing a msgbox

Go into the VBE and do Ctrl+G to see the immediate window (or
View=Immediate window).

The second code should work if they are from the control toolbox
toolbar.

I have tested it and it worked fine for me.

--
Regards,
Tom Ogilvy


"Didier Poskin" wrote in message
...
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" a écrit dans le message de
...
"as checkbox" refers to a checkbox from the forms toolbar. Are
your
checkboxes from the Forms toolbar or the control toolbox toolbar?
It
makes
a big difference in how you can refer to them.

From the forms toolbar:

Sub tester10()
Dim chkbx As CheckBox
For Each chkbx In ActiveSheet.CheckBoxes
Debug.Print chkbx.Name & " - " & chkbx.TopLeftCell.Address
Next

End Sub

From the control toolbox toolbar

Sub Tester11()
Dim obj As OLEObject
Dim chkbx As MSForms.CheckBox
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.CheckBox Then
Set chkbx = obj.Object
Debug.Print obj.Name & " - " & chkbx.Name _
& " - "; obj.TopLeftCell.Address
End If
Next
End Sub

in xl2000 and later, the OleObject name, Shape Name and Control Name
are
coordinated, but not true in xl97.

--
Regards,
Tom Ogilvy


" wrote in 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




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






---
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 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
-----Original Message-----
Dave,

I'm lost again:

I get this error msg : "Unable to get OLEObject property
of the worksheet
Class"

Quid?
Any further advice?

Thank you in advance
Rgrds,

Didier
"Dave Ramage" a écrit dans le message
de
...
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


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


.