Can anyone tell me if it is possible to insert a number of pictures (one
for each cell in a column) automatically. I can do this manually but
would like to automate the process for say 100 rows. I have the picture
jpeg number in an adjacent cell in the same row.

Thanks in advance


--
tryer
------------------------------------------------------------------------
tryer's Profile: http://www.excelforum.com/member.php...o&userid=16546
View this thread: http://www.excelforum.com/showthread...hreadid=466666

One way...

Option Explicit
Sub testme()
Dim myCell As Range
Dim myRng As Range
Dim testStr As String
Dim PictFileName As String

Set myRng = Selection
For Each myCell In myRng.Cells

PictFileName = myCell.Offset(0, -1).Value

testStr = ""
On Error Resume Next
testStr = Dir(PictFileName)
On Error GoTo 0

If testStr = "" Then
'do nothing, picture not found
Else
If myCell.Comment Is Nothing Then
myCell.AddComment Text:="new comment here!" 'or ""
End If
myCell.Comment.Shape.Fill.UserPicture PictFileName
End If

Next myCell

End Sub

This assumes that the full filename of the picture that should be used is to the
left of the cell.

Select your range that should get the comments and try it out.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

tryer wrote:

Can anyone tell me if it is possible to insert a number of pictures (one
for each cell in a column) automatically. I can do this manually but
would like to automate the process for say 100 rows. I have the picture
jpeg number in an adjacent cell in the same row.

Thanks in advance

--
tryer
------------------------------------------------------------------------
tryer's Profile: http://www.excelforum.com/member.php...o&userid=16546
View this thread: http://www.excelforum.com/showthread...hreadid=466666

--

Dave Peterson

Sub AddPictures()
Dim shp As Picture
For Each cell In Range("B3:B30")
cell.Select
Set shp = ActiveSheet.Pictures.Insert( _
"C:\images" & cell.Value & ".jpg")
Next
End Sub

might be a start.

--
Regards,
Tom Ogilvy

"tryer" wrote in
message ...

Can anyone tell me if it is possible to insert a number of pictures (one
for each cell in a column) automatically. I can do this manually but
would like to automate the process for say 100 rows. I have the picture
jpeg number in an adjacent cell in the same row.

Thanks in advance


--
tryer
------------------------------------------------------------------------
tryer's Profile:
http://www.excelforum.com/member.php...o&userid=16546
View this thread: http://www.excelforum.com/showthread...hreadid=466666

Darn subject line questions <vbg!

Tom Ogilvy wrote:

Sub AddPictures()
Dim shp As Picture
For Each cell In Range("B3:B30")
cell.Select
Set shp = ActiveSheet.Pictures.Insert( _
"C:\images" & cell.Value & ".jpg")
Next
End Sub

might be a start.

--
Regards,
Tom Ogilvy

"tryer" wrote in
message ...

Can anyone tell me if it is possible to insert a number of pictures (one
for each cell in a column) automatically. I can do this manually but
would like to automate the process for say 100 rows. I have the picture
jpeg number in an adjacent cell in the same row.

Thanks in advance


--
tryer
------------------------------------------------------------------------
tryer's Profile:
http://www.excelforum.com/member.php...o&userid=16546
View this thread: http://www.excelforum.com/showthread...hreadid=466666


--

Dave Peterson

Dave,

Thank you for that advice. I have edited the filenames and paths an
got it working OK. Now I am trying to resize the inserted picture. Th
code I have added is:-
myCell.Comment.ShapeRange.LockAspectRatio = msoTrue
myCell.Comment.ShapeRange.Height = 143.25
myCell.Comment.ShapeRange.Width = 248.25

Unfortunately this does not work. Any suggestions

--
trye
-----------------------------------------------------------------------
tryer's Profile: http://www.excelforum.com/member.php...fo&userid=1654
View this thread: http://www.excelforum.com/showthread.php?threadid=46666

I would think that if you lock the aspect ratio, then you only have to change
the width or the height--not both.

I just added a couple of more lines to the existing routine...

Else
If myCell.Comment Is Nothing Then
myCell.AddComment Text:="new comment here!" 'or ""
End If
myCell.Comment.Shape.Fill.UserPicture PictFileName
myCell.Comment.Shape.LockAspectRatio = msoTrue
myCell.Comment.Shape.Height = 143.25
End If



tryer wrote:

Dave,

Thank you for that advice. I have edited the filenames and paths and
got it working OK. Now I am trying to resize the inserted picture. The
code I have added is:-
myCell.Comment.ShapeRange.LockAspectRatio = msoTrue
myCell.Comment.ShapeRange.Height = 143.25
myCell.Comment.ShapeRange.Width = 248.25

Unfortunately this does not work. Any suggestions?

--
tryer
------------------------------------------------------------------------
tryer's Profile: http://www.excelforum.com/member.php...o&userid=16546
View this thread: http://www.excelforum.com/showthread...hreadid=466666

--

Dave Peterson