parameter driven image in excel

[scarg]

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

 

[joerd]

Put this code into the worksheet_change event procedure:

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.

 

[scarg]

Joerd,

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

 

[joerd]

This goes into the worksheet code section.
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.

 

[scarg]

Greetings Joerd,

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

 

[joerd]

Probably easiest if you send me the file at joerd68@yahoo.com

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[scarg]

Greetings Joerd,

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