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

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

Phil Hageman wrote:
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, Microsoft MVP - Excel
http://www.andypope.info

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" wrote:

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

Phil Hageman wrote:
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, Microsoft MVP - Excel
http://www.andypope.info

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


Phil Hageman wrote:
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" wrote:


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

Phil Hageman wrote:

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, Microsoft MVP - Excel
http://www.andypope.info


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info