Using Names instead of Shape Index Numbers

paisiello

New Member
Joined
Jun 14, 2012
Messages
7
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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,784
Office Version
  1. 2010
Platform
  1. Windows
Your code is fine, if the right sheet is active and the shape name is correct.

There's no need to select:

Code:
ActiveSheet.Shapes("Freeform 1362").Delete
 

paisiello

New Member
Joined
Jun 14, 2012
Messages
7
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?
 
Last edited:

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,784
Office Version
  1. 2010
Platform
  1. Windows
Code:
Worksheets("Sheet1").Shapes("Freeform 1362").Delete

Change the sheet name as appropriate.
 

paisiello

New Member
Joined
Jun 14, 2012
Messages
7

ADVERTISEMENT

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

MrExcel MVP
Joined
May 7, 2008
Messages
21,784
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,784
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Code:
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

New Member
Joined
Jun 14, 2012
Messages
7
If you want to use a defined name, you could do it as
Code:
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 way:(1) ActiveWorkbook.Names.Add Name:="Shape_Name_1", RefersTo:=Selection.NameThen when you try and get the assigned name of the shape using:(2) 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

MrExcel MVP
Joined
May 7, 2008
Messages
21,784
Office Version
  1. 2010
Platform
  1. Windows
Shapes("Shape_Name") requests the member of the shapes collection named Shape_Name, of which there is none.

Instead of

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

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

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

Sandcastor

Board Regular
Joined
May 14, 2012
Messages
97
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]
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,808
Members
416,983
Latest member
LessThanAverageUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top