Moving a shape with VBA

[NewOrderFac33]

Good afternoon,

If I want to move a shape on a worksheet, the following recorded code works:

ActiveSheet.Shapes.Range(Array("Rounded Rectangle 2")).Select
Selection.ShapeRange.IncrementLeft 0.1

but the following doesn't:

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

 

[Rick Rothstein]

I think I would do it this way instead...

With ActiveSheet.Shapes.Range(Array("Rounded Rectangle 2"))
  .Left = .Left + 0.1
End With

 

[RoryA]

You don't need the ShapeRange:

ActiveSheet.Shapes("Rounded Rectangle 2").IncrementLeft 0.1

 

[NewOrderFac33]

Gentlemen,

Both solutions work - thank you!

Here's the complete thing with Rick's solution currently commented out.

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]

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

 

[NewOrderFac33]

Gawd bless ya' Guv'nor!

'Ave an 'appy Christmas!

 

[shg]

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]

That was extremely helpful, Shg. Thanks for taking the time to post such a detailed explanation!

Pete

 

[shg]

You're welcome, Pete.