CODEWITHSUNDEEP
vbaexcelshapes
Ellie
Ellie

Reputation: 43

Selecting a shape in Excel with VBA

I am trying to select a shape by name using VBA. In my sheet, I have 10 shapes named 1 to 10 and want a specific one. For some reason, the shape that excel selects with my code does not seem to be the shape with the name I told it to select. As far as I can tell, the shape that it chooses is random. Here is my code (The x value is supposed to retrieve a number value that I input from 1 to 10):

Sub FindTheShape()

Sheets("Fleet 1").Select
Dim x As Long
x = ActiveSheet.Range("$A$1000").End(xlUp).Value

ActiveSheet.Shapes(x).Select

End Sub

Please help, any ideas are greatly appreciated.

Upvotes: 4

Views: 24351

Answers (2)

JohnyL
JohnyL

Reputation: 7162

As far as I can tell, the shape that it chooses is random.

This happens because when you're using numeric value, you're in fact referring to the position of a shape in Shapes collection. When you use string value, you're referring to shape's name. The same goes for sheets. See my answer.

Upvotes: 0

EvR
EvR

Reputation: 3498

Your x gives the index-number not the name, when your shapes are named 1 to 10 make a string from your x:

ActiveSheet.Shapes(CStr(x)).Select

Upvotes: 5

Related Questions