Insert Picture using Macro & Validation list

[marc747]

Hi,

I have an excel file and in Cell "L21" I have a Validation list that
contains Picture names I want to see if I could place a Macro that
every time I select a picture name from the Validation list macro
would look into a folder (C:\Temp\Pix\) that contains all the pictures
(some .jpg, .gif. etc.) and insert it in cell "L10" and resize the
picture to Height 42 by keeping the Aspect Ratio. If no pictures were
found insert default picture called "NO Pic"

Thanks,
Marc

 

[Joel]

You code would then only run from a single PC that has all the picture.
Another mthod is to put all the pictures into a workbook. Resize them
manually and put them ontop of each other. You can make one visiable and
make all the others invisible. then when you select the validation list move
the picurte you want to the top of the other picture and make it visible and
the others invisible.

 

[marc747]

It's OK ti run on single PC, Is there any other way besides loading
all on a workbook.

Thanks,
Marc

 

[Bernie Deitrick]

Marc,

You could use the worksheet change event. Copy this code, right-click the
sheet tab, select "View Code" and insert the code into the window that
appears. I have assumed that the file name in cell L21 includes the file
extension.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myScale As Double
If Target.Address <> "$L$21" Then Exit Sub

'Select the cell where the picture is placed
Application.EnableEvents = False
On Error Resume Next
ActiveSheet.Shapes("KnownPictureName").Delete
On Error GoTo 0

Range("L10").Select
'Insert the picture
On Error GoTo NoPic
ActiveSheet.Pictures.Insert("C:\Temp\Pix\" & Range("L21").Value).Select
GoTo GotPic
NoPic:
ActiveSheet.Pictures.Insert("C:\Temp\Pix\No Pic.jpg").Select
GotPic:
'scale the picture to the width of the column
myScale = 42 / Selection.ShapeRange.Height
Selection.Name = "KnownPictureName"
Selection.ShapeRange.ScaleWidth myScale, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight myScale, msoFalse, msoScaleFromTopLeft
Range("L22").Select

Application.EnableEvents = True
End Sub

 

[marc747]

Hi,

Thanks, but the file name does not include the file extension. Can we
add a line so that it can look for the most common extensions.

Marc

 

[Gord Dibben]

Couldn't you just add the extension to the filename in L21

filename.jpg or .bmp or whatever.


Gord Dibben MS Excel MVP