Selecting a shape in Excel with VBA

Question

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.

EvR · Accepted Answer · 2018-07-03 14:53:20Z

5

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

answered Jul 3, 2018 at 14:53

EvR

3,5082 gold badges17 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JohnyL · Accepted Answer · 2018-07-03 19:12:54Z

0

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.

answered Jul 3, 2018 at 19:12

JohnyL

7,2045 gold badges27 silver badges48 bronze badges

Collectives™ on Stack Overflow

Selecting a shape in Excel with VBA

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related