When a picture is inserted into an Excel worksheet using Shapes.AddPicture(...) method, Excel gives it a name "Picture 1", "Picture 2" etc automatically.
This name can be used to get a reference to this shape object in Shapes collection like Shapes.Item("Picture 1"). If the name is changed in Excel using the Name Box, there are two different names (or one of them is a key/Caption) through which the Shape object can be referenced. So if I change the name to "MyPic" I can use any of these to reference a shape in Shapes collection:
Shapes.Item("Picture 1")
OR
Shapes.Item("MyPic")
The name can be accessed using Shape.Name property in VBA but how can we access the other value (MyPic) that does not seem to change internally?
UPDATED
What I am trying to do is to link a cell to a picture in Excel. I keep the picture data in cell's comment. These are the scenarios:
For me getting the internal name is important. I have the Shape reference but no idea how to get the internal name from this ref.
On the Developer tab, in the Shape Design group, click Shape Name.
Under Text Box Tools, on the Format tab, in the Text Box Styles group, click Change Shape, and then click the shape that you want.
Immediately after you have added the shape to a worksheet (oSht) you can use oSht.Shapes(Osht.Shapes.count)
to reference it.
So, oSht.Shapes(osht.shapes.count).Name
will give you its name.
If you want to find the index of a shape in the Shapes collection and you know its name then you need to loop through Shapes.Name
until you find it. If you know the Index, then you can construct the "Picture n" alternate name, or you can store the "Picture n" alternate name. You can also store the ID property of the shape and then reference the shape by looping through the Shapes collections until you find the Shape.ID
If the user moves the shape to a different sheet and then renames it, there is no way of identifying it as the original shape, because the external name, alternate name, Shapes index, and ID will all be different. So, if this is a problem in your scenario you would need to consider shadow copying or sheet protection.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With