Help with getting an image to automatically change in Excel

Zetta

New Member
Joined
Apr 28, 2018
Messages
3
I need an image in my document to automatically change based on the selections in two other cells

Cell B2 contains a Combo Box with a Drop Down list
Cell C2 contains a Combo Box with a Drop Down list
Cell B10 contains a formula that uses the contents of cells B2 and C2 to output the file name of the image that needs to be shown
The images are saved in a folder named PICS, there are several thousands of them
The to left corner of the image needs to align with Cell F2
The image needs to automatically change when either cell B2 or cell C2 changes, or when the formula updates the contents of cell B10

I've tried getting this to work on my own, but I have no experience with VBA or Macros and need help.
 

ask2tsp

Active Member
Joined
Feb 18, 2015
Messages
484
Paste the following code in the code module of the worksheet. (right-click on the sheet's label and select view code).
This is a piece of code that works for me. You have to make a few minor corrections as indicated in the comments.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim pic As Picture, newPic As Picture, ps As ShapeRange
    Dim ratio As Single, anchor As Range
    
    If Target.Address(0, 0) = "C3" Then
    'ín your case use
    'If Target.Address(0, 0) = "cb1" or Target.Address(0, 0) = "cb2"
    'where cb1 and cb2 are the cells connected to the comboboxes
        
        'the cell to anchor the topleft of the pic to
        Set anchor = Range("F2")
        
        'the next line assumes there is one picture on your sheet
        Set pic = Pictures(1)
        Set ps = pic.ShapeRange
        
        'the next line assumes the path is included
        '(you can do that in the formula that builds the filename)
        Set newPic = Pictures.Insert(Range("B10").Value)
        
        With newPic.ShapeRange
            .Top = anchor.Top
            .Left = anchor.Left
            ratio = .Height / .Width
            .Height = ps.Height
            .Width = .Height / ratio
        End With
        pic.Delete
    End If
End Sub
 

Zetta

New Member
Joined
Apr 28, 2018
Messages
3
Thank you very much, but I could not get it to work with the Combo Boxes and had to switch to regular Drop Down Lists.
 

ask2tsp

Active Member
Joined
Feb 18, 2015
Messages
484
The change event only occurs if the change is caused by someone typing a value in the target field. In your case you have to simulate that. One way of doing that could be to attach this code to your Ok button
Code:
Private Sub cb1_Click()
'change     cb1 to the objectname of your Ok button
    ActiveSheet.Range("B10").Select
    SendKeys "{F2}{ENTER}"
    UserForm1.Hide
'change UserForm1 to the objectname of your form
End Sub
 

Zetta

New Member
Joined
Apr 28, 2018
Messages
3
After over a year I've decided to rebuild this project, and while the cell locations may have changed the setup and issue is the same.


Cell L2 is connected to ComboBox1, a drop down list
Cell L3 is connected to ComboBox2, also a drop down list
Cell B14 outputs a file location based on the contents of L2 & L3
Cell B28 outputs a different file location based on the contents of L2 & L3
Cell B2 contains Picture1, saved at the location in B14
Cell B16 contains Picture2, saved at the location in B28


I need Picture1 & Picture2 to automatically change when cells B14 and B28 change, which both change based on ComboBox1 & ComboBox2. Inserting the images into another worksheet is not an option, as there are over 100,000 of them.

The previous examples had worked wonderfully, however I can not get them to work now. I suspect this is because I've switched from Form Control ComboBoxes to ActiveX Control ComboBoxes, but I'm not 100% sure about that and any help would be appreciated.
 

Forum statistics

Threads
1,078,134
Messages
5,338,428
Members
399,232
Latest member
stevenmoritz

Some videos you may like

This Week's Hot Topics

Top