You insert a picture onto a sheet--not into a cell.

But I made some other changes, too. I don't like variables that look too much
like VBA's reserved words--Sheet and Count scare me.

I also declared Pic to be a picture.

I was also confused about why you checked the length of the value in column "A",
but then use the value in column "I".

If Len(sheet.Cells(b, 1)) 0 Then
strFilePath = "C:/graphics/" & Cells(b, 9).Value

Option Explicit
Public Sub DisplayPics2()
Dim b As Long
Dim strFilePath As String
Dim pic As Picture
Dim mySheet As Worksheet
Dim myCount As Long

'Start at row 2 as the first row is a header row.
b = 2

Set mySheet = Worksheets("sheet2")
myCount = mySheet.UsedRange.Rows.Count

'Insert column to insert picture.
mySheet.Cells(1, 3).EntireColumn.Insert

'look for the jpg file name in column i,
'find the file in the local drive and insert
'the picture in column C

While b <= myCount
strFilePath = "C:\my documents\my pictures\" _
& mySheet.Cells(b, 9).Value
If Len(Trim(mySheet.Cells(b, 9).Value)) = 0 Then
mySheet.Cells(b, 3).Value = "No Photo Available"
ElseIf Dir(strFilePath) = "" Then
mySheet.Cells(b, 3).Value = "No Photo Available"
Else
Set pic = mySheet.Pictures.Insert(strFilePath)
With pic
.Top = mySheet.Cells(b, 3).Top
.Left = mySheet.Cells(b, 3).Left
.Width = mySheet.Cells(b, 3).Width
.Height = mySheet.Cells(b, 3).Height
End With
pic.ShapeRange.ScaleWidth 0.5, msoFalse, msoScaleFromTopLeft
pic.ShapeRange.ScaleHeight 0.5, msoFalse, msoScaleFromTopLeft
Rows(b).RowHeight = 90
End If

b = b + 1
Wend

End Sub

Lil wrote:

Hi,

I am new to writing Excel Macros and have been working on this for a
couple of days now and can't seem to get it going. Someone please
help!!

I have a worksheet in which one column holds the file name. I need to
find this file in a directory on the C:/drive and display this picture
in a cell in the appropriate row.

Here's some code that I tried but didn't work:

Public Sub DisplayPics()
Dim b As Long
Dim strFilePath As String
Dim pic As Shape

'Start at row 2 as the first row is a header row.
b = 2

Set sheet = Worksheets("Sheet2")
count = sheet.UsedRange.Rows.count

'Insert column to insert picture.
Worksheets("Sheet2").Cells(1, 3).EntireColumn.Insert

'look for the jpg file name in column i, find the file in the local
drive and insert
'the picture in column C

While b <= count
If Len(sheet.Cells(b, 1)) 0 Then
strFilePath = "C:/graphics/" & Cells(b, 9).Value

If Dir(strFilePath) = "" Then
Worksheets("Sheet2").Cells(b, 3).Value = "No Photo
Available"
Else
Worksheets("Sheet2").Cells(b, 3).Pictures.Insert
(strFilePath)
Set pic = ActiveSheet.Shapes(Activesheeet.Shape.count)
Selection.ShapeRange.ScaleWidth 0.5, msoFalse,
msoScaleFromTopLeft 'This is on a single line
Selection.ShapeRange.ScaleHeight 0.5, msoFalse,
msoScaleFromTopLeft 'This is on a single line
Rows(b).RowHeight = 90

End If
End If

b = b + 1

Wend

End Sub

At this line "Worksheets("Sheet2").Cells(b, 3).Pictures.Insert
(strFilePath)", the error is: "Object does not support this property or
method"

Any suggestions would be great.

Many thanks in advance for your help,
Lillian.

--
Lil
------------------------------------------------------------------------
Lil's Profile: http://www.msusenet.com/member.php?userid=1810
View this thread: http://www.msusenet.com/t-1870503977

--

Dave Peterson