Checkboxes and Arrays

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
Fri May 09 12:35:57 CDT 2008

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

baconcow
Fri May 09 12:52:01 CDT 2008

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

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

Dave
Fri May 09 13:50:44 CDT 2008

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

baconcow wrote:
>
> 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" wrote:
>
> > 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 wrote:
> > >
> > > 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
> >

--

Dave Peterson