VBA change Picture 1

theta

Well-known Member
Joined
Jun 9, 2009
Messages
960
Hi

I have an image on my workbook (tab called 'Section 1'). The image was just pasted into the workbook.

I need to make it so that when I change the selection of a data validation dropdown (client 1, client 2, client 3)...the image will change.

The logos are stored in the same dir of the workbook, in a sub-folder called 'Images'

So when 'client 1' is selected, named range of 'Selection' becomes 'client 1' and the VBA changes the picture to .\Images\client 1.bmp

Any ideas, better suggestions, examples?
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
See if you can adapt this:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$A$1" Then Exit Sub
    Dim Pic As Picture
    If Me.Pictures.Count Then Me.Pictures(1).Delete
    Target.Offset(, 2).Select
    Set Pic = Me.Pictures.Insert(Me.Parent.Path & "\Images\" & Target.Value & ".bmp")
End Sub
 
Upvote 0
Looks promising

Do I have to define a cell "$A$1" or could I substitute with the named range Selection. And how would I make this change

After this, I should be ok the adapt...

Thanks!
 
Upvote 0
That's what I am hoping to change. Can I reference to a named range instead of a cell?

Then I can move the ref cell without it being hardcoded in the macro as it will be a named range
 
Upvote 0
If the data validation cell is named Clients you can change the first line to:

Code:
If Target.Address <> Range("Clients").Address Then Exit Sub
 
Upvote 0
Thanks Andrew

My VBA logic is fine, just working on the referencing...!

One issue, I have several pics in my worksheet, I only want a specific one to change. Do I have to give it a specific reference, or can I refer to it as Picture 1.

From looking at the code, it deletes the picture (1) ?
 
Upvote 0
Name the picture eg PicTemp. Then try:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> Range("Clients").Address Then Exit Sub
    Dim Pic As Picture
    On Error Resume Next
    Me.Pictures("PicTemp").Delete
    On Error GoTo 0
    Target.Offset(, 2).Select
    Set Pic = Me.Pictures.Insert(Me.Parent.Path & "\Images\" & Target.Value & ".bmp")
    Pic.Name = "PicTemp"
End Sub
 
Upvote 0
Thanks Andrew, legend!

Just notices 42k+ posts, ouch! You have a seperate screen just for Mr Excel at your work? Ha
 
Upvote 0
Hi Andrew

When I make a selection using a data validation dropdown I get an error. The logo space is on another sheet "Section 1" and this is where the change needs to happen

This is the code. The Range("LogoSpace") is giving me teh 1004 error

Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> Range("SelectedClient").Address Then Exit Sub
    Dim Pic As Picture
    On Error Resume Next
    Me.Pictures("LOGO").Delete
    On Error GoTo 0
   Range("LogoSpace").Select
    Set Pic = Me.Pictures.Insert(Me.Parent.Path & "\IMAGES\" & Target.Value & ".bmp")
    Pic.Name = "LOGO"
End Sub

Even using Sheets("Section 1").Range("D1").Select fails
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top