Paste Graphic in Cell's Center

[Guest]

I have a graphic (bull's eye) frequently used to signify completion of a
task. It is copy/pasted into a cell - the problem is, I have to move it
around manually to ge it in the center of the cell. A real problem with
different size cells. Is there a way to make the graphic fit exactly in the
center of the cell on paste?

Thanks, Phil

 

[Andy Pope]

Hi Phil,

The following will position the latest shape on the activesheet in the
center of the active cell. Hopefully you can modify the code to suit you
particular code.

Sub PasteCenter()

Dim shpTemp As Shape

If ActiveSheet.Shapes.Count > 0 Then
' use latest shape
Set shpTemp = ActiveSheet.Shapes(ActiveSheet.Shapes.Count)

shpTemp.Left = ActiveCell.Left + _
((ActiveCell.Width - shpTemp.Width) / 2)
shpTemp.Top = ActiveCell.Top + _
((ActiveCell.Height - shpTemp.Height) / 2)

Set shpTemp = Nothing
End If

End Sub

Cheers
Andy

 

[Guest]

Thanks, Andy. I named this code Sub CenterPastedObjects, and put it in
Module 1 of my Personal.xls. However, I cant get it to work - at all. Where
am I going wrong?

Thanks, Phil

 

[Andy Pope]

Hi Phil,

If no error is occurring then the IF THEN test must be failing.
The code doesn't actually paste anything it only works for the last
shape placed on the activesheet.

If there is an error can you let me know what it is.

Cheers
Andy