Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Display an image depending on user selection

    I have a workwheet with an input cell where the user can select, for example, either Apple, Banana or Cherry. And whichever is selected, I need an image to display a picture of the selected fruit.
    The images (.jpeg's, say) would be stored on another sheet.
    Now I could accomplish it using VBA code that would be triggerred by the change to the input cell (delete old image, copy in the new image), but is there a better way that anybody knows of?
    I tried storing the images in comment boxes, combined with a user defined function, but I'm struggling with how to populate an image (rather than a single cell).
    Thanks

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Colin,

    This should work:
    Code:
    Option Explicit
    
    Private Sub ListBox1_Click()
    
      MsgBox "Current Selection:" & vbCrLf & Range("A1").Text, _
             vbOKOnly + vbInformation
       Sheet1.imgFruit.Picture = LoadPicture(CurDir() & "\Test\" & Range("A1").Text & ".jpg")
      
    End Sub
    Note: the picture is displayed in an image control named imgFruit. Also that the image control will not work with .png files.
    Attached is my test workbook.
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by RetiredGeek; 2011-11-06 at 11:18.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    ColinBurrows (2011-11-06)

  4. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post
    Thanks, RG. The workbook needs to be self-contained though - i.e., it can't assume that the user has the required images stored on their hard drives. So I really need the images to be stored in the workbook.
    As I understand it, LoadPicture only works on external files. I've tried referencing something like Sheets("Fruit").Shapes.Range(Array("imgApple")), but can't get it to work.
    When I looked online, I ended up on Stephen Bullen's site, with a complicated solution involving Windows API calls. But maybe that's the only way to do it...

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Colin,

    Ok, try this one.

    1. I created Picture controls for each picture.
    2. Loaded the picture for each using the Properties dialog.
    3. Gave each a real name, e.g. imgApple, etc.
    4. Aligned/Sized all controls so they overlap.
    5. Changed the On_Click event of the listbox as follows.
    6. Moved the pictures to a different location using Windows Explorer so I know they weren't being loaded.

    Code:
    Private Sub ListBox1_Click()
    
      imgApple.Visible = False
      imgOrange.Visible = False
      imgPear.Visible = False
      
      MsgBox "Current Selection:" & vbCrLf & Range("A1").Text, _
             vbOKOnly + vbInformation
             
      Select Case Range("A1").Text
          Case "Apple"
              imgApple.Visible = True
          Case "Orange"
              imgOrange.Visible = True
          Case "Pear"
              imgPear.Visible = True
      End Select
      
    End Sub
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Using Ole Objects

    If you use Embeded OleObjects (Active X Image controls) rather than pictures you can set
    the picture of one control to the picture in another.
    Note. you will need to turn Design Mode on for the Object to change and set properties.

    I have no idea how you could do this without a bit of vba though.
    In this case, the images can be stored on another sheet as embeded images.

    See attached example

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Dim imgO As OLEObject, fShape As Boolean, imgC As OLEObject
    Dim rIsect As Range, rTarget As Range
    
    'Check for only a single cell selection
    'You can refine the error trapping here
    
    'Are we selecting cells
    If TypeName(Selection) <> "Range" Then Exit Sub
    'Is there only one cell in the range
    If Selection.Rows.Count * Selection.Columns.Count <> 1 Then Exit Sub
    
    Set rIsect = Intersect(Target, Range("imagecells"))
    If Not rIsect Is Nothing Then
        fShape = False
        For Each imgO In Sheets("Sheet2").OLEObjects
            If imgO.Name = "Image" & Target Then
                Set imgC = imgO
                fShape = True
                Exit For
            End If
        Next
        If fShape Then
            Sheets("sheet1").OLEObjects("Image1").Object.Picture = imgC.Object.Picture
        End If
    End If
    
    End Sub


    ExcelChangeImage.jpg
    Attached Files Attached Files
    Last edited by AndrewKKWalker; 2011-11-06 at 20:18.
    Andrew

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Andrew,

    I think we're playing with the same kind of control? I like your code because you don't have to bother overlaying the pictures. I've never worked too much with Ole Objects so I'll be dissecting your code to learn. Thanks.

    I almost forgot, I wanted to do something using the Worksheet_Change event but found that it does not fire when the ListBoxes linked cell value changes or did I do something wrong?
    Attached Images Attached Images
    Last edited by RetiredGeek; 2011-11-06 at 20:30.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #7
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    As far as I know, you cannot trigger the worksheet_change event this way, and WithEvents is only supported in a Class Module.
    Of course, if someone else wants to come along and educate us then that would be wonderful.
    Andrew

  9. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Here's a no code way.

    BTW, you can call events from other code - they are routines too.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  10. The Following User Says Thank You to rory For This Useful Post:

    ColinBurrows (2011-11-29)

  11. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Rory,

    Ok, you got me. I found the Defined Names, I found the Text in the cells behind the pictures, but I'll be darned if I can figure how this all works. Care to enlighten me and maybe a few others?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Select the picture on the front sheet and have a look at the formula bar.
    The picture is linked to the named range, which returns a range using an index match, so the picture returns an image of that range. It's basically what the Camera tool does.
    Last edited by rory; 2011-11-08 at 01:36. Reason: Additional detail
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Rory,

    I saw that but didn't know you could do that! I poked around {excel 2007} and finally figured out I had to enable the Developer Tab {via Excel Options} to be able to get into Design Mode. Once in Design Mode I checked the properties dialog and noticed the property EnableCalculations attached to the picture and it all made sense. Needless to say I do the majority of my work in Office 2003 and only use 2007 when on my laptop and then not usually doing development work. Thanks again for your innovative solution...another trick for my bag.
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  14. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    The EnableCalculation property refers to the worksheet, not the picture (which is not an ActiveX control). You can do exactly the same thing using the camera tool in all versions of Excel as far back as I remember.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •