Can you select pictures from drop-down lists?

  • Thread starter Thread starter Arthur
  • Start date Start date
A

Arthur

Is it possible to make a text drop-down list that can make pictures pop
up in another cell? For example, I have a list of companies that I
also have logos for. I would like to select the company name from a
drop-down list, and then have their logo appear in a specified cell.
Is this possible? If it's not possible for a drop-down list, is there
any other way to have specific images appear in excel based on list
selection?

thanks!!
 
Arthur,

I think you would need VBA.The picture would not be in the cell, as shapes
exist in a separate layer to the worksheet cells, but you could position it.

What you could do is name the logo by the value in the drop-down, and then
move that to the required position, move all others away,.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
What do you mean by naming the logo by the value in the drop-down? ar
you saying this would somehow enable me to move a logo into place o
the worksheet simply by selecting an item in the list
 
Arthur,

The problem is that when you select something, you need a way to align that
selection to the picture object. That is what I was suggesting was a method
of aligning them. What I mean is that if the entry in the drop-down is say
'Mercury Inc' then name the picture with that same value. But you still
don't get it move automatically, you need to write some code to do that.
This worksheet event code will do it assuming that the drop-down is
DataValidation

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Shape
Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("A13")) Is Nothing Then
With Target
If .Value <> "" Then
For Each sh In ActiveSheet.Shapes
sh.Left = 5000
Next sh
With ActiveSheet.Shapes(.Value)
.Left = 100
.Top = 100
End With
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Wow! Thanks for writing that code.

But the problem is I don't know how to execute it! I tried copying i
into VB by going Insert > Procedure (and selecting Sub, Private) but
don't know what that did, if anything! Could you show me the righ
way?

a
 
Arthur,

On the worksheet that you want the pictures, right-click on the sheet tab.
This will bring up a menu, select View Code from the menu, and this will
open up the VBA with the worksheet code module opened up. Paste that code
into there. If you drop-down is not in A13, change that part of the code to
suit.

Go back to Excel, load your pictures into the sheet, load the drop-down
list, then select something. You may then want to tune where the picture
moves to.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
That's a little better! But I'm still having some troubles.

I have four jpegs in the worksheet with filenames of "Pic 1", "Pic 2"
etc.. and a Data Validation drop-down list with values equal to th
file names. The problem is that no matter which Pic I select from th
list, -all- of the pictures shift. I would like it so that only one o
them shifts.

Another anomoly I noticed was that I could enter any values in th
drop-down list and still -all- the pictures would shift.

Can you help me work out these kinks
 
Arthur,

I'm confused as to the problem. They do all shift, as you want 1 in the
visible scope, 3 out of it.

If the DV was setup properly, you would only be able to enter one of the
valid values.

Why not send me the workbook (bob . phillips @ tiscali. co uk - remove the
spaces), and I will fix it.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top