moving shapes with vba absolute X and Y coordinates

[Lochnagar]

Hi,

In a previous post I read, it mentioned that it's possible to specify absolute coordinates for an excel shape e.g. rectangle. I've tried recording a macro whilst moving a shape around but to no avail, see below,

ActiveSheet.Shapes("Rectangle 1").Select
Selection.ShapeRange.IncrementLeft -20
Selection.ShapeRange.IncrementTop -20

...and I've tried looking thru help, but I'm having difficulty working out what the appropriate commands are for absolute corrdinates instead of relative ones. So I was hoping someone might be able to point me in the right direction.

Any advise/help will, as always, be hugely appreciated.

Thanks,
Lochnagar.

 

[Gary McMaster]

Try something like this:

Public Sub Test()

Dim oShape As Shape

Set oShape = ActiveSheet.Shapes("Rectangle 1")

oShape.Top = 200
oShape.Left = 200

'or

'Move shape to top left corner of cell E10
Dim oCell As Range

Set oCell = ActiveSheet.Range("E10")

oShape.Top = oCell.Top
oShape.Left = oCell.Left


End Sub

Hope it helps.

Gary

 

[GlennUK]

What about:

ActiveSheet.Shapes("Rectangle 1").Top = 45
ActiveSheet.Shapes("Rectangle 1").Left = 33

?

 

[Lochnagar]

Brilliant. Thanks GlennUK and Gary, just what I was after.

Thanks again,
Lochnagar

 

[tcesxerxes]

i would like the user to drag a shape to certain cell, i.e. C4. If he drags it to a cell other than C4, an error message will appear... can someone help me with this...thanks!