Inserting a Picture using Cell as picture name.

In the module of the worksheet, paste in the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim stFile As String
If Not Intersect(Target, Me.Range("E6")) Is Nothing Then
stFile = "C:\Temp\" & Me.Range("E6") & ".JPG"
If Dir(stFile) = "" Then
MsgBox "File not found"
Else
Me.Shapes("NewPic").Delete
With Me.Shapes.AddPicture(stFile, True, True, Range("A16").Left, _
Range("A16").Top, Range("A16").Width, Range("A16").Width * 3 / 4)
.Name = "NewPic"
End With
End If
End If
End Sub



You will need to adjust the directory in the line beginning stFile And you will need to name the picture currently in A16 as NewPic
(select it, type the name NewPic in the box at the left end of the
formula bar and press Enter)

Then, when you enter the name of a picture file in E6, the relevant
picture should appear in A16.

It will be sized in 4 * 3 format - you can change that too by modifying
"* 3 / 4"

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Bill Manville;728252 Wrote:

In the module of the worksheet, paste in the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim stFile As String
If Not Intersect(Target, Me.Range("E6")) Is Nothing Then
stFile = "C:\Temp\" & Me.Range("E6") & ".JPG"
If Dir(stFile) = "" Then
MsgBox "File not found"
Else
Me.Shapes("NewPic").Delete
With Me.Shapes.AddPicture(stFile, True, True, Range("A16").Left, _
Range("A16").Top, Range("A16").Width, Range("A16").Width * 3 /
4)
.Name = "NewPic"
End With
End If
End If
End Sub



You will need to adjust the directory in the line beginning stFile > And you will need to name the picture currently in A16 as NewPic
(select it, type the name NewPic in the box at the left end of the
formula bar and press Enter)

Then, when you enter the name of a picture file in E6, the relevant
picture should appear in A16.

It will be sized in 4 * 3 format - you can change that too by modifying

"* 3 / 4"

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Thats great and I hope it works, however there is only 1 problem. I
have never used Modules so dont know where to begin. I will look around
the forum for a guide and let you know how I get on.
I am using 2007 and still getting to grips with it.

Thanks for the reply.

Carl




the-jackal

Bill,

When you paste this program in a cell, it gives an error message. Is there a
particular place or way that it needs to be pasted? I placed an equal sign in
the cell I chose to paste it in so it will not show up as just text.

-JFGatorAlum

"Bill Manville" wrote:

In the module of the worksheet, paste in the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim stFile As String
If Not Intersect(Target, Me.Range("E6")) Is Nothing Then
stFile = "C:\Temp\" & Me.Range("E6") & ".JPG"
If Dir(stFile) = "" Then
MsgBox "File not found"
Else
Me.Shapes("NewPic").Delete
With Me.Shapes.AddPicture(stFile, True, True, Range("A16").Left, _
Range("A16").Top, Range("A16").Width, Range("A16").Width * 3 / 4)
.Name = "NewPic"
End With
End If
End If
End Sub



You will need to adjust the directory in the line beginning stFile > And you will need to name the picture currently in A16 as NewPic
(select it, type the name NewPic in the box at the left end of the
formula bar and press Enter)

Then, when you enter the name of a picture file in E6, the relevant
picture should appear in A16.

It will be sized in 4 * 3 format - you can change that too by modifying
"* 3 / 4"

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Alt+F11 to the Visual Basic editor
View > Project Explorer
Expand the Microsoft Excel Objects
Double-click the worksheet in which the relevant cells reside
The code window for that worksheet will appear in the upper right
window; paste the code in there.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

JFGatorAlum wrote:

When you paste this program in a cell, it gives an error message

Yes, it would,
I said paste it in the module of the worksheet - you need to be in the
visual basic editor to do that. See other post for details.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup