Excel VB query

JEB

JEB

Associate
Joined
19 Oct 2002
Posts
1,468
Location
Caithness, Scotland
I wonder if someone can help with a bit of Excel VB please.

In this file

File

I have two drop-down lists. One in cell A2, the other A14. On changing these cells I want a picture to be displayed that corresponds to the cell entry.

I have this working fine with Cell A2, but I don't know how to add another section into the VB code to also get it to work on cell A14. When working I need a picture displayed for each drop-down option?

Can anyone help me out here please?
 

s-p

s-p

Associate
Joined
18 Oct 2002
Posts
611
Location
UK
Could you not use the same bit code again which works for the first section?

Code:
    Private Sub Worksheet_Calculate()
        Dim oPic As Picture
        Me.Pictures.Visible = False
        With Range("F1")
            For Each oPic In Me.Pictures
                If oPic.Name = .Text Then
                    oPic.Visible = True
                    oPic.Top = .Top
                    oPic.Left = .Left
                    Exit For
                End If
            Next oPic
        End With
        With Range("F13")
            For Each oPic In Me.Pictures
                If oPic.Name = .Text Then
                    oPic.Visible = True
                    oPic.Top = .Top
                    oPic.Left = .Left
                    Exit For
                End If
            Next oPic
        End With    
    End Sub

It should be noted that one of the pictures won't display if both cells have the same values.
 

JEB

JEB

Associate
Joined
19 Oct 2002
Posts
1,468
Location
Caithness, Scotland
Excellent, thank you for that, works a treat.

When you import pictures from a file it automatically names then 'Picture 1', 'Picture 2' etc. Do you know how to rename them once they have been imported?

Thanks again.
 

s-p

s-p

Associate
Joined
18 Oct 2002
Posts
611
Location
UK
Apologies for the delay in getting back to you.

From recollection, you need to select the picture who's name you want to adjust. Then in the "Name Box" prompt (by default sits below New Document Shortcut on the toolbar), rename the entry from "Picture X" to "NewPictureName".

You may have used the "Name Box" prompt previously to set the name for a range of cells, or quickly select a formula function.

Any problems, give me a shout.
 

JEB

JEB

Associate
Joined
19 Oct 2002
Posts
1,468
Location
Caithness, Scotland
Thanks for the reply s-p. I tried the rename in the name box, but it wasn't working, and then voilla - the trick was once you renamed it you have to hit 'enter' for it to accept it. Previously I was renaming and just clicking off it again.

Thanks for your help, you are a super-star.
 
Top