parameter driven image in excel
parameter driven image in excel
(OP)
Greetings,
I have what is most likely an easy question for most of you.
I have a pull down list with about 5 different product families. Lets say the cell G5 contains one of the following values depending on what is picked in the pull down list-
NJ916
NJ916R
NJB
TT
TD
I also have 5 small images named identical to the values listed above.
In cell G11 I want to show one of the name images based on the value in cell G5 that is driven by the pull down menu.
Pretty simple stuff, however I just can't seem to make it happen.
Thank you for your consideration.
Steve
I have what is most likely an easy question for most of you.
I have a pull down list with about 5 different product families. Lets say the cell G5 contains one of the following values depending on what is picked in the pull down list-
NJ916
NJ916R
NJB
TT
TD
I also have 5 small images named identical to the values listed above.
In cell G11 I want to show one of the name images based on the value in cell G5 that is driven by the pull down menu.
Pretty simple stuff, however I just can't seem to make it happen.
Thank you for your consideration.
Steve





RE: parameter driven image in excel
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Target.Address = "$G$5" Then
For Each c In Me.Range("ListSource")
Me.Shapes(c.Value).Visible = False
Next c
Me.Shapes(Target.Value).Visible = True
End If
End Sub
I have defined a range named "ListSource" that refers to the range where you have the elements of your dropdown list.
I have put all the images in the worksheet, on top of each other (you can obviously place them anywhere you want). The event procedure should hide all but one of them once you change the value of G5
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: parameter driven image in excel
Thank you for the response. Where would I insert this code?
I am afraid that I will stick it in the wrong section and create a big mess.
Does it go into the script editor?
Thank you for you help and Happpy Holidays.
Steve
RE: parameter driven image in excel
Open the Visual Basic Editor (press Alt-F11)
You should see a window divided into sections, one of which is the project explorer (if you don't see it, press Ctrl-R)
In the Project Explorer, select your workbook (probably called something like VBAProject (Book1), doubleclick to expand (like in the windows explorer). Then you see an item Microsoft Excel Objects, doubleclick, and you will see a list of the sheets in the workbook, plus an item called ThisWorkbook.
Select the appropriate worksheet (the one that the pictures are on), and doubleclick.
A window will open, which is called something like Book1 - Sheet1 (Code)
From the listbox at the top that contains (General), select the Worksheet item. Some text will appear in the editor window.
From the listbox at the right (currently at SelectionChange), select Change.
This will automatically insert the first and last line of the code that I posted earlier. You can copy and paste the other lines in between.
Still with me? You should be done by now... Close the Visual Basic Editor, or switch back to Excel, and test if it works!
Good luck
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: parameter driven image in excel
I inserted the string above and really can't get anything to happen. I have named all of my objects etc.... and the code was pasted directly from the text string above.
Does this have anything to do with how I originally inserted the pictures, as objects or pictures- or whether I simply copy and pasted the images in from the clipboard?
Any help is very much appreciated. I can send you the file if you are interested in taking a look at it.
I look eagerly toward you response.
Best Regards,
Steve
RE: parameter driven image in excel
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: parameter driven image in excel
Because of proprietary information contained in the spreadsheet I decided it would be best if I did not send. I do appreciate the help however. After much code mining off of vba help and tip sites - here is what I ended up doing. Thank you for your assistance.
Public Sub SelectionChange()
Dim Selection As Long
On Error Resume Next
Select Case ActiveSheet.Shapes(Application.Caller).FormControlType
Case xlListBox
Application.ScreenUpdating = False
For Selection = 1 To Names(Application.Caller).RefersToRange.Count
ActiveSheet.Shapes(ActiveSheet.Shapes(Application.Caller).Name & Selection).Visible = (Selection = Range(ActiveSheet.Shapes(Application.Caller).ControlFormat.LinkedCell))
Next Selection
Application.ScreenUpdating = True
End Select
End Sub
Thank you for your support.
Best Regards,
Steve