Macro to find a JPEG & insert

[Guest]

I have a worksheet ("Winners Log") which has a list of JPEG numbers in column
A. I want to click on any JPEG number, have Excel find that JPEG photo in
the "Winners Photo" folder on my Drive, and copy that photo to Cell E3 on
the "Posters" worksheet. I would appreciate any help you may offer.

 

[Bernie Deitrick]

Larry,

Try the event below. Copy the code, right click on the Winners Log sheet
tab, select view code, and paste the code in the window that appears.

This assumes that the numbers in column A are valid file names with the
exception of the .jpg extension.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next
If Target.Column <> 1 Then Exit Sub

myName = "D:\Winners Photo\" & _
Target.Value & ".jpg"

Application.EnableEvents = False
With Worksheets("Posters")
.Select
.Shapes("Inserted").Delete
.Range("E3").Select
.Pictures.Insert(myName).Select
Selection.Name = "Inserted"
.Range("A1").Select
End With
Worksheets("Winners Log").Select

Application.EnableEvents = True
End Sub

 

[Bernie Deitrick]

Larry,

You just need to have macros enabled, and events enabled. You also must have
put the macro into the correct code-module - not a regular code module. If
you didn't right-click the correct sheet tab to select "View Code" you might
have pasted it in the wrong place.

Try this macro to enable events:

Sub EnableEvents()
Application.EnableEvents = True
End Sub

If you can't get this to work, I will send you a file with the code working.
Just reply to my munged email address.

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Larry and everybody else,

Larry had sheet protection turned on, which wouldn't allow the macro to
insert the file.

Bernie
MS Excel MVP