Autosizing picture to fit a cell

[Lor66]

What I have done is merge a large number of cells together to form one large cell for the purpose of adding pictures to the spreadsheet. However, some of the pictures are larger than the cell. What I would like to do it format the cell somehow so that any picture placed within it will auto resize to fit within the cell. Ideally, it should be easy for the user to add a picture into the cell. In other words, it will not do if the use has to insert a picture, then go through the process of resizing it to fit the cell; the picture needs to automatically adjust in size when placed withing the given cell.

Any ideas? Much thanks

[Emily]

 
Sub TestInsertPictureInCell()
InsertPictureInRange "D:\Happy1.jpg", Range("A1")
End Sub


Sub InsertPictureInRange(PictureFileName As String, TargetCells As Range)
Dim p As Object

If Dir(PictureFileName) = "" Then Exit Sub

Set p = ActiveSheet.Pictures.Insert(PictureFileName)

With TargetCells
    p.Top = .Top
    p.Left = .Left
    .ColumnWidth = .ColumnWidth / .Width * p.Width
    .RowHeight = p.Height  ' < 409 pixels
End With

Set p = Nothing
End Sub