Checkboxes and Arrays

[baconcow]

When working with Check Boxes, is it not possible to use arrays to keep track
of them, as with worksheets?

SET UP

Dim all_boxes As Variant

' setting up ranges
Set range_create = Worksheets("INTRO").Range("F7")


What I am trying to do:

' create array listing the names of every possible sheet (there are MANY
more sheets than listed here)
all_boxes = Array("create_111", "create_112", "create_113")

' select all other check boxes by making them true
For offset_var = 0 To 2 (there are normally 27 check boxes)
Worksheets("INTRO").Array(all_boxes(offset_var)).Value = True
Next offset_var


What works:

Worksheets("INTRO").create_111 = True
Worksheets("INTRO").create_112 = True
Worksheets("INTRO").create_113 = True

' etc... this takes up a LOT of space, and I always like trying to make more
efficient code, if possible


Problem:

"Object doesn't support this property or method"


Attempts to fix it:

- I have tried putting the names in the array without "" (quotation marks)

- I have tried removing the Array
Worksheets("INTRO").(all_boxes(offset_var)).Value

- I have tried without the Value
Worksheets("INTRO").(all_boxes(offset_var))

- I have tried using Shapes with and without Value
Worksheets("INTRO").Shapes(all_boxes(offset_var))
Worksheets("INTRO").Shapes(all_boxes(offset_var)).Value


It is problem something extremely simple...

Thanks for any help!

 

[Dave Peterson]

dim all_boxes as variant
dim bCtr as long
all_boxes = Array("create_111", "create_112", "create_113")

with Worksheets("INTRO")
for bctr = lbound(all_boxes) to ubound(all_boxes)
.oleobjects(all_boxes(bctr)).object.value = false
next bctr
end with

(Untested, uncompiled. Watch for typos.)

 

[baconcow]

Hey,

I really need to get used to the bounds. That is the second time the bounds
have held me back. I think I understand them too. The code seems to
automatically find out the upper and lower bounds without having to display
them. In the end, this code worked and compiled great:


With Worksheets("INTRO")
If select_all.Value = True Then
For bound = LBound(all_boxes) To UBound(all_boxes)
.OLEObjects(all_boxes(bound)).Object.Value = True
Next bound
Else
For bound = LBound(all_boxes) To UBound(all_boxes)
.OLEObjects(all_boxes(bound)).Object.Value = False
Next bound
End If
End With


You're really great with this stuff. You must do this for your occupation,
correct?

 

[Dave Peterson]

It looks like you could use this:

With Worksheets("INTRO")
For bound = LBound(all_boxes) To UBound(all_boxes)
.OLEObjects(all_boxes(bound)).Object.Value = select_all.Value
Next bound
End With