Center All Pictures in Excel Cells

[Finny]

Please help. Right now I have a UDF that I found online that lets me
insert a picture into a cell by referencing the exact location on my
hard drive where the picture resides. I also have a macro that if I
select a shape # and range value, it will center that shape in the
cell for which is referenced.

Is there a way to combine these two codes to get it to insert the
picture and also center the picture perfectly within the cell? I can
get the first code to add as many pictures as I reference, but then I
am at a loss with centering them all within the cells.

Any help that you can provide would be greatly appreciated.


<<<<INSERT PICTURE CODE START>>>>

Function ShowPic(PicFile As String) As Boolean
Dim AC As Range
Dim shp As Shape
On Error GoTo Done
Set AC = Application.Caller
ActiveSheet.Shapes.AddPicture PicFile, True, True, AC.Left, AC.Top, 5,
5
ShowPic = True
Exit Function
Done:
ShowPic = False
End Function

<<<<INSERT PICTURE CODE END>>>>

<<<<CENTER PICTURE CODE START>>>>
Sub aTest()
CenterMe ActiveSheet.Shapes(1), Range("b8")
End Sub

Sub CenterMe(shp As Shape, overcells As Range)

With overcells
shp.Left = .Left + ((.Width - shp.Width) / 2)
shp.Top = .Top + ((.Height - shp.Height) / 2)
End With
End Sub

<<<<CENTER PICTURE CODE END>>>>

 

[Jim Cone]

Function ShowPic(PicFile As String) As Boolean
Dim AC As Range
Dim shp As Shape
On Error GoTo Done

Set AC = Application.Caller
Set shp = ActiveSheet.Shapes.AddPicture _
(PicFile, True, True, AC.Left, AC.Top, 10, 10) '<<< picture made larger
Call CenterMe(shp, AC) ' <<< calls the centering sub.
ShowPic = True
Set shp = Nothing
Set AC = Nothing
Exit Function
Done:
ShowPic = False
End Function
'--
Sub CenterMe(shp As Shape, overcells As Range)
With overcells
shp.Left = .Left + ((.Width - shp.Width) / 2)
shp.Top = .Top + ((.Height - shp.Height) / 2)
End With
End Sub
--

Jim Cone
Portland, Oregon USA
( http://www.mediafire.com/PrimitiveSoftware )





"Finny" <[email protected]>
wrote in message Please help. Right now I have a UDF that I found online that lets me
insert a picture into a cell by referencing the exact location on my
hard drive where the picture resides. I also have a macro that if I
select a shape # and range value, it will center that shape in the
cell for which is referenced.

Is there a way to combine these two codes to get it to insert the
picture and also center the picture perfectly within the cell? I can
get the first code to add as many pictures as I reference, but then I
am at a loss with centering them all within the cells.
Any help that you can provide would be greatly appreciated.


<<<<INSERT PICTURE CODE START>>>>

Function ShowPic(PicFile As String) As Boolean
Dim AC As Range
Dim shp As Shape
On Error GoTo Done
Set AC = Application.Caller
ActiveSheet.Shapes.AddPicture PicFile, True, True, AC.Left, AC.Top, 5,
5
ShowPic = True
Exit Function
Done:
ShowPic = False
End Function

<<<<INSERT PICTURE CODE END>>>>

<<<<CENTER PICTURE CODE START>>>>
Sub aTest()
CenterMe ActiveSheet.Shapes(1), Range("b8")
End Sub

Sub CenterMe(shp As Shape, overcells As Range)

With overcells
shp.Left = .Left + ((.Width - shp.Width) / 2)
shp.Top = .Top + ((.Height - shp.Height) / 2)
End With
End Sub

<<<<CENTER PICTURE CODE END>>>>