Using VBA to insert an image within a cell

Ekim said:

Rick,

Here’s one way.

Setup:
In column E, put the names of your images.
In column D, put the path to the images in column E.
In column C:
=D4&"\"&E4

e.g.

a_PRIME Insert pictures in WS.xls
	C	D	E	F
3	*	*	*	*
4	C:\mypictures\widget01.jpg	C:\mypictures	widget01.jpg	Picture*here
5	*	*	*	*
6	*	*	*	*
7	*	*	*	*
8	*	*	*	*
9	*	*	*	*
10	*	*	*	*
11	C:\My*documents\Picture\razoos.gif	C:\My*documents\Picture	razoos.gif	Picture*here
12	*	*	*	*
13	*	*	*	*
14	*	*	*	*
15	*	*	*	*
16	*	*	*	*
17	C:\My*Documents\Images\Pictures\thingies.jpg	C:\My*Documents\Images\Pictures	thingies.jpg	Picture*here
18	*	*	*	*
19	*	*	*	*
Sheet3

The following macro from Dave Peterson (Microsoft MVP) looks at the reference in column C, and places the image in column F (see Notes below). Put the macro in a standard module.

Code:

Sub testme01()
' Dave Peterson
Dim myPict As Picture
Dim curWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim myPictName As Variant

Set curWks = Sheets(1)   ' Change to suit

curWks.Pictures.Delete

With curWks
    Set myRng = .Range("C2", .Cells(.Rows.Count, "C").End(xlUp))
End With

For Each myCell In myRng.Cells
    If Trim(myCell.Value) = "" Then
        'do nothing
    ElseIf Dir(CStr(myCell.Value)) = "" Then
        'picture not there!
        MsgBox myCell.Value & " Doesn't exist!"
    Else
        With myCell.Offset(0, 3) '3 columns to the right of C (F)
            Set myPict = myCell.Parent.Pictures.Insert(myCell.Value)
            myPict.Top = .Top
            myPict.Width = .Width
            myPict.Height = .Height
            myPict.Left = .Left
            myPict.Placement = xlMoveAndSize
        End With
    End If
Next myCell

End Sub

Notes:
1. See this line:

With myCell.Offset(0, 3)

Means 3 columns to the right of C i.e. column F. Change to suit. This is where the images will be placed.

2. You will have to manually size the cells in column F to fit the image. See these lines:
myPict.Top = .Top
myPict.Width = .Width
myPict.Height = .Height
myPict.Left = .Left

The above code will give the impression that the images fit within the cell (as Parry states, images do not actually sit inside a cell – with Windows, the images “float” in the Drawing level above the cell).

3. Columns C, D and E can be hidden if you wish.

Regards,

Mike

Click to expand...

razzandy said:

I'll be using this sometime soon.

Thanks Guys

Click to expand...