align autoshape with centre / right of excel cell

[chris_culley]

Hi all,

I want to align an autoshape using vba with some cells on a
spreadsheet... I picked up the following from this newsgroup a while
back:


"Use something like the following to align the top left hand corner of
the
copied shape to the G column and the active cell row:

Sheets("Data").Shapes("Up Arrow").Copy
ActiveSheet.Paste
With Cells(ActiveCell.Row, "G")
Selection.Top = .Top
Selection.Left = .Left
End With


John Green (Excel MVP) "

which is great - works perfectly (cheers John!), but I want to align to
the centre right of the cell, not the top left! is this possible?

Many thanks

chris

 

[Tom Ogilvy]

please explain what center right means.

if you mean the right side of the cell, isn't that the left side of the cell
to the right.

Selection.Left = .offset(0,1).Left

to get the center of a cell height wise

ActiveCell.Top + activeCell.height / 2

Selection .Top = .Top + .height / 2

 

[chris_culley]

thanks Tom - you are a genius

please explain what center right means.

if you mean the right side of the cell, isn't that the left side of the cell
to the right.

Selection.Left = .offset(0,1).Left

to get the center of a cell height wise

ActiveCell.Top + activeCell.height / 2

Selection .Top = .Top + .height / 2