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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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
 

theta

Well-known Member
Joined
Jun 9, 2009
Messages
960
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!
 

theta

Well-known Member
Joined
Jun 9, 2009
Messages
960

ADVERTISEMENT

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
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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
 

theta

Well-known Member
Joined
Jun 9, 2009
Messages
960

ADVERTISEMENT

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) ?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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
 

theta

Well-known Member
Joined
Jun 9, 2009
Messages
960
Thanks Andrew, legend!

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

theta

Well-known Member
Joined
Jun 9, 2009
Messages
960
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:

Forum statistics

Threads
1,141,730
Messages
5,708,143
Members
421,549
Latest member
Dtcfire

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
Top