Using Names instead of Shape Index Numbers

[paisiello]

I am having a hard time figuring out how to make this work so hopefully someone can assist me.

1) When I try the following code in my macro it works fine:

ActiveSheet.Shapes(1).Select
Selection.Delete

But when I substitue a name like this:

ActiveSheet.Shapes("Shape_Name").Select
Selection.Delete

I get "The item with the specified name doesn't exist" error message.

2) When I go to the name manager it shows that "Shape_Name" has the Value of "Freeform 1362" and Refers To "=Freeform 1362". When I click on the object it shows as "Freeform 1362" in the name window.

What am I missing? I am writing this as a VBA Module and want to refer to all shapes by the name I give them.

TIA

 

[shg]

Your code is fine, if the right sheet is active and the shape name is correct.

There's no need to select:

ActiveSheet.Shapes("Freeform 1362").Delete

 

[paisiello]

Thanks for the quick reply. The shape name is correct.

I put in a variable to tell me the ActiveSheet:

Temp = ActiveSheet.Name

and it looks to be the right one.

What else could be the problem?

 

[shg]

Worksheets("Sheet1").Shapes("Freeform 1362").Delete

Change the sheet name as appropriate.

 

[paisiello]

That can't be it because if I put the index number in it works fine. There is something preventing it from getting the variable name. I can get other variable names from the spreadsheet without a problem.

Using Excel 2010.

 

[shg]

The shape name you should be using is the name that appears in the Names box, left of the formula bar, when you select the shape.

 

[shg]

If you want to use a defined name, you could do it as

Worksheets("Sheet1").shapes([shape_name]).delete

That retrieves the value of the name.

Your code is trying to delete the shape named "Shape_Name"

 

[paisiello]

If you want to use a defined name, you could do it as

Worksheets("Sheet1").shapes([shape_name]).delete

That retrieves the value of the name.Your code is trying to delete the shape named "Shape_Name"

Thanks very much, that works! I am still confused why [] works and "" doesn't. Is it possible that the way that the shape was named influences the way VBA interprets shape names? For example, here is one way to name a shape: Selection.ShapeRange.Name = "Shape_Name_1" Selection.Name = "Shape_Name_1"And here is another way1) ActiveWorkbook.Names.Add Name:="Shape_Name_1", RefersTo:=Selection.NameThen when you try and get the assigned name of the shape using2) VarName = ActiveSheet.Shapes(1).NameThis returns different results. Using the first method I get VarName="Shape_Name_1". The seond method returns "Freeform 1402".

 

[shg]

Shapes("Shape_Name") requests the member of the shapes collection named Shape_Name, of which there is none.

Instead of

Worksheets("Sheet1").shapes([shape_name]).delete

you could make explicit reference to the Evaluate function (which is what the brackets do implicitly):

Worksheets("Sheet1").Shapes(Evaluate("shape_name")).Delete

 

[Sandcastor]

Wow, shg is everywhere. Shg, I would like to future-proof a workbook for my colleages. The current version does not have a specific group of images, but an upcoming release does. Trying to delete a non-existing object gives a 400 error.
What is the syntax for checking if Sheets("Directions").Shapes.Range(Array("Picture 1")) exists, before deleting it? I'm still new to IF statements.

___________________
[WIT] Remarks [/WIT]