VBA change Picture 1

[theta]

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?

 

[Andrew Poulsom]

See if you can adapt this:

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]

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!

 

[Andrew Poulsom]

The code assumes that your data validation is in A1. Change to suit.

 

[theta]

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]

If the data validation cell is named Clients you can change the first line to:

If Target.Address <> Range("Clients").Address Then Exit Sub

 

[theta]

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]

Name the picture eg PicTemp. Then try:

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]

Thanks Andrew, legend!

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

 

[theta]

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

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