align autoshape with centre / right of excel cell
 

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

align autoshape with centre / right of excel cell
 

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

--
Regards,
Tom Ogilvy


" wrote:

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

align autoshape with centre / right of excel cell
 

thanks Tom - you are a genius :)



Tom Ogilvy wrote:
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

--
Regards,
Tom Ogilvy


" wrote:

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