How do edit a Specific Shape?

[ptmuldoon]

I have workbook that includes a Shape with a hyperlink to 'Return to Index' of the workbook. That shape is called "Rounded Rectangle 2"

Is there to edit the that shape name in VBA? I would like to change the actual shape to perhaps to smaller size (and perhaps to an oval).

I think Once I understand that, I can then loop all the worksheets and update that shape through out the entire workbook.

[StephenR]

Why not try recording a macro while you make changes to the shape and taking it from there? Post back with some code if you get stuck.

[Kevin#]

Unfortunately Excel does not contain a Morph function. So cannot turn one shape into another.
So what needs to be done is to insert a new shape (Oval), attach a hyperlink to it, and delete the old shape (Rounded Rectangle).


Here is a macro that will copy a shape (based on its name) from the active sheet to cell A1 in every other sheet.
To run in attached workbook {ctrl} + t

Please Login or Register  to view this content.

[ptmuldoon]

Thanks Kevin.

I haven't tried to change the shape yet, but that would make sense to need to create a new one and delete the new one.

For now, I'm trying to just edit the existing Shape.

I used the recorded to do some changes. But I can't seem to figure how to determine and Shape Name to loop and only update that particular shape in other worksheets.

Please Login or Register  to view this content.

[Kevin#]

To find the shape name - select it and look in the name box (bottom left of ribbon)


Edit
Looking at your code the name is probably "Rounded Rectangle 1"
Worth check that it is called the same name in all sheets

[ptmuldoon]

Thanks Kevin

Making progress, and the below is currently working

Please Login or Register  to view this content.

[Kevin#]

Good -
Do you need the same line repeated?

If you are only making one change, try this line

Please Login or Register  to view this content.

and delete lines

Please Login or Register  to view this content.