adding pictures via dropdown menu?

[d0wnt0wn]

is there any way that I can set up my spreadsheet so that if i select something from a menu in lets say a5 that a pre-determined picture will appear over cells a1-4

 

[Oaktree]

This was an adaptation to some Andrew Poulsom code on the board:

Dim MyLocn As String
Static MyPic As Picture
On Error Resume Next
MyPic.Delete
On Error GoTo 0
MyLocn = Sheets("Data").Range("J1").Offset(WorksheetFunction.Match(Sheets("Info Sheet").Range("selected_part"), Sheets("Data").Range("A:A"), 0) - 1)
Range("A1").Select
Set MyPic = ActiveSheet.Pictures.Insert(MyLocn)
With MyPic
.ShapeRange.LockAspectRatio = msoFalse
.ShapeRange.Height = 371.25
.ShapeRange.Width = 361.5
End With

Create a lookup table with looked up value and path (such as C:\my_picture.jpg) of the corresponding picture.

Then, change the line that starts with MyLocn = to reference your lookup table, and change the .ShapeRange.Height and .ShapeRange.Width to whatever they need to be in order to fit into your range.

 

[d0wnt0wn]

sorry Oak I dont quite understand... is there any way you can give me a simplified example?

 

[Oaktree]

Sure...

If this is your data, where B2 is the input cell for the label of the picture you want to insert, and D2:E3 is a table which maps the labels to the picture locations (as below), then change

 MyLocn = Sheets("Data").Range("J1").Offset(WorksheetFunction.Match(Sheets("Info Sheet").Range("selected_part"), Sheets("Data").Range("A:A"), 0) - 1)

to

MyLocn = Range("E2").Offset(WorksheetFunction.Match(Range("B2"), Range("D2:D3"), 0) - 1)

Then, just resize your picture accordingly, and make the changes to the code's .ShapeRange.Height = 371.25 and .ShapeRange.Width = 361.5 lines.

Book19
	A	B	C	D	E
1		What to Look Up		Lookup Table
2		Picture 1		Picture 1	C:\pic1.jpg
3				Picture 2	c:\pic2.jpg
Sheet1

 

[d0wnt0wn]

ok thanks i think i can figure it out now... ill let you know

 

[d0wnt0wn]

ok i changed the line as you said and pasted this in my worksheet 1 module

Dim MyLocn As String
Static MyPic As Picture
On Error Resume Next
MyPic.Delete
On Error GoTo 0
MyLocn = Range("d2").Offset(WorksheetFunction.Match(Range("a2"), Range("c2:c10"), 0) - 1)
Set MyPic = ActiveSheet.Pictures.Insert(MyLocn)
With MyPic
.ShapeRange.LockAspectRatio = msoFalse
.ShapeRange.Height = 371.25
.ShapeRange.Width = 361.5
End With

I tried to run it and got an invalid outside procedure message

 

[Oaktree]

Put it in a sheet module as part of a worksheet change event:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyLocn As String
Static MyPic As Picture
On Error Resume Next
MyPic.Delete
On Error GoTo 0
MyLocn = Range("d2").Offset(WorksheetFunction.Match(Range("a2"), Range("c2:c10"), 0) - 1)
Set MyPic = ActiveSheet.Pictures.Insert(MyLocn)
With MyPic
.ShapeRange.LockAspectRatio = msoFalse
.ShapeRange.Height = 371.25
.ShapeRange.Width = 361.5
End With
End Sub

The macro will fire anytime the sheet (whose module you placed the code in) is changed.

 

[d0wnt0wn]

ok i got that part...... but now when i try to run it it says....

unable to get the match property of the worksheet function class

 

[d0wnt0wn]

ok.... i am moving along somewhat... and I am sure that eventually this will be what i need... here is whats going on now...

I had some of the ranges mixed up and I have corrected that.... here is the code as I am using it now

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyLocn As String
Static MyPic As Picture
On Error Resume Next
MyPic.Delete
On Error GoTo 0
MyLocn = Range("d2").Offset(WorksheetFunction.Match(Range("a2"), Range("c2:c10"), 0) - 1)
Set MyPic = ActiveSheet.Pictures.Insert(MyLocn)
With MyPic
.ShapeRange.LockAspectRatio = msoFalse
.ShapeRange.Height = 100
.ShapeRange.Width = 100
End With
End Sub


in row C i have a number which will represent the picture i want to show up
row D is the path and filename
row E is the description I also want to come up with it
row A is the row i will enter a number into to make this happen

now it works to the point where if i click in cell a2 and enter the number 1 then that picture appears.... awsome!!!...but...
if i type in the number 2 in A2 then i get this error

"unable to get the insert property of pictures class"

any ideas?


also no matter what number i type in cell A3-a10 only the first picture in my list appears