Moving a shape with VBA

NewOrderFac33

Well-known Member
Joined
Sep 26, 2011
Messages
1,268
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,866
Office Version
  1. 2019
  2. 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
38,772
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,268
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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
38,772
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. ;)
 

NewOrderFac33

Well-known Member
Joined
Sep 26, 2011
Messages
1,268
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
Gawd bless ya' Guv'nor!

'Ave an 'appy Christmas!
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,833
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,268
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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,833
Office Version
  1. 2010
Platform
  1. Windows
You're welcome, Pete.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,550
Messages
5,832,418
Members
430,132
Latest member
Bdesai

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