×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

parameter driven image in excel

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

RE: parameter driven image in excel

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.

RE: parameter driven image in excel

(OP)
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

RE: parameter driven image in excel

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.

RE: parameter driven image in excel

(OP)
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

RE: parameter driven image in excel

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.

RE: parameter driven image in excel

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources