Naming shapes - revised and re-posted

[Ian Jones]

Is there a way of referencing, in code, a Shape object
other than using its's string name?

Problem
Excel allows duplicate names:

If I have 5 shapes (rectangles, ovals, triangles, etc) and
name them each "Shape"..
and in code I..

ActiveSheet.Shapes("Shape").Select

....this will select the first Shape laid onto the Sheet
that was renamed "Shape"

Is there another way of referencing an individual shape?
I'm fishing for something like an array subscript.

I'm looking for a quick and efficient way of processing a
large number of shape objects.

i.e. If I could assign an array of shapes individual
names using an array subscript....

Cheers, Ian

 

[Chip Pearson]

Ian,

You can use a For Each loop to loop through all the shapes on a sheet. E.g.,

Dim SH As Shape
For Each SH In ActiveSheet.Shapes
Debug.Print SH.Name
Next SH

You can also use the numeric index in to the Shapes collection. E.g.,

Dim N As Long
For N = 1 To ActiveSheet.Shapes.Count
Debug.Print ActiveSheet.Shapes(N).Name
Next N

Problem
Excel allows duplicate names:

How do you name the shapes with duplicate names? Excel shouldn't allow
duplicate names. Are you naming them manually with in the Names box, or are
you creating them with code? What version of Excel are you using?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)