Is it possible to Select an array of excel objects? 2

[PWD]

Good afternoon. My colleague has created an Excel sheet with 'Up or Down' indicators and wants to be able to select an array of these objects. I believe they are named (locally?) Box 1 to Box 140. Is it possible to build up an array of these object names and then select that array. We have scoured the web and also tried to build-up the names - to no avail. Manually selecting 2 objects while recording a macro gives:-

    ActiveSheet.shapes.Range(Array("Box 1", "Box 2")).Select

But if we try to build something called "Boxes" we get the string "Box 1, Box 2", which then is of no use!!

I believe that these aren't the only objects on the sheet either.

Any ideas please?

Many thanks,
D€$

 

[SkipVought]

hi,

My colleague has created an Excel sheet with 'Up or Down' indicators

one hundred fourty of them!!! Holy mackerel, Bat Man!

Have you thought of maybe using only ONE? I'm not kidding! Based on the CONTEXT of a SELECTION on the sheet, your code could make visible or not, and position & size the control as required relative to the selected cell.

Short of this approch, you can always initialize an array of the control indexes, by looping thru the shapes on the sheet. Then use your array.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[combo]

I totally agree with Skip relating to the design of worksheet.

In case of an array of names and selection of related objects:

Dim Boxes(1 To 140)
For i = 1 To 140
    Boxes(i) = "Box " & i
Next i
ActiveSheet.Shapes.Range(Boxes).Select

combo

 

[PWD]

Thanx guys, we'd got the 'Dim' wrong. Works a treat!!

Many thanks,
D€$

 

[SkipVought]

Glad you shined some light on your Dim.

I like being a Dim wit!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]