Moving a shape with VBA

NewOrderFac33

Well-known Member
Joined
Sep 26, 2011
Messages
1,255
Good afternoon,

If I want to move a shape on a worksheet, the following recorded code works:
Code:
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 2")).Select
Selection.ShapeRange.IncrementLeft 0.1
but the following doesn't:
Code:
ActiveSheet.Shapes("Rounded Rectangle 2").ShapeRange.IncrementLeft 0.1
reporting "Object doesn't support this property or method"

It seems a bit inefficient to have to keep selecting the shape before I move it.

Would anyone care to point out how remarkably stupid I'm being?

Regards

Pete
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,694
Office Version
  1. 2010
Platform
  1. Windows
I think I would do it this way instead...
Code:
With ActiveSheet.Shapes.Range(Array("Rounded Rectangle 2"))
  .Left = .Left + 0.1
End With
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,118
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You don't need the ShapeRange:
Code:
ActiveSheet.Shapes("Rounded Rectangle 2").IncrementLeft 0.1
 
Last edited:

NewOrderFac33

Well-known Member
Joined
Sep 26, 2011
Messages
1,255
Gentlemen,

Both solutions work - thank you!

Here's the complete thing with Rick's solution currently commented out.
Code:
Public StopMacro As Boolean


Private Sub CommandButton1_Click()
    ActiveSheet.Shapes("Rounded Rectangle 2").IncrementLeft 0.1
    'With ActiveSheet.Shapes.Range(Array("Rounded Rectangle 2"))
    '    .Left = .Left + 0.1
    'End With
End Sub


Private Sub CommandButton1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    StopMacro = True
    Do While StopMacro
        ActiveSheet.Shapes("Rounded Rectangle 2").IncrementLeft 0.1
        'With ActiveSheet.Shapes.Range(Array("Rounded Rectangle 2"))
        '    .Left = .Left + 0.1
        'End With
        DoEvents
    Loop
End Sub


Private Sub CommandButton1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    StopMacro = False
End Sub

If you're interested, CommandButton1 is an ActiveX worksheet command button.
If you click it once, the shape moves to the right. If you click it and keep the mouse button held down, it continues to move the shape until the mouse button is released.
I'm sure there's a practical application for this somewhere...

...or then again, maybe not.

Thank you both!

Pete

P.S. Do I get a prize when I get to 1000 posts..? :)
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,118
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Yes - a bonus of 50% of what we currently pay you. ;)
 

shg

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

ADVERTISEMENT

I can't articulate this clearly, but ...

When you record modifying an object, you select it (as you must), then change it. The recorder shows someObject.Select (because that's what you did), then Selection.Property = value. That works, and is repeatable. Then you try to edit the code to omit the Select, as all good coders will, but find that someObject.Property = value doesn't work.

The Selection.Property = value works because the object model regards the Selection object as a hierarchy (you pick a shape, you got the shape object, the shaperange, the textframe, the ...), and Excel navigates down the hierarchy until it finds something with that property or method. I reckon the object model could be equally forgiving when we try to operate directly on the top-level object, but it ain't.
 

NewOrderFac33

Well-known Member
Joined
Sep 26, 2011
Messages
1,255
That was extremely helpful, Shg. Thanks for taking the time to post such a detailed explanation!

Pete
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
You're welcome, Pete.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,947
Messages
5,599,012
Members
414,273
Latest member
vikas007

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