How can I create a dropdown list in Excel 2003 using autoshapes or an image?

Question

I want to create a dropdown list in excel using an image or an autoshape so that users can select only the images/autoshapes that I want. I would also consider using a dropdown of a number and then have an adjacent cell display an image/autoshape as well.

Answer 1

This can be done using the Control Toolbox and VBA.    You can use images, autoshapes, or a combination of the two.  

Here is the process to create one using Autoshapes.

View  >  Toolbars

Select the Control Toolbox toolbar.

When it appears on your spreadsheet, click on the Combo box icon (right side, third from the bottom). Click on your spreadsheet and drag to insert it.

Size it as you like, though you may have to resize it later for all of your text to be readable inside the box.

Right click on it and select 'Properties'.

When the Properties window opens, scroll down to 'Linkedcell' and enter 'T10' (no quotes).

In ListFillRange enter 'T1:T8' (no quotes).

Close back to Excel.

Open your Drawing toolbar, select Autoshapes, Basic Shapes, and select a Rectangle.  Click on the worksheet, drag and enter the rectangle.  Make note of the autoshape name in the upper left portion of the sheet.  It will be something like 'Rectangle 4' ,etc.  You WILL NEED this info later.

Repeat the process and select a Triangle and an Oval.  Make note of their names, also.  For some reason, triangles appear to be simply named Autoshape 6, etc, instead of 'Triangle'.

Position all three autoshapes, on top of each other, just to the right of your combo box.

Next, in cell T1 enter 'Rectangle'  (no quotes).  Enter 'Triangle' in T2 and 'Oval' in T3.

Copy this macro to the clipboard:

If ActiveSheet.Range("T10").Value = "Triangle" Then

    ActiveSheet.Shapes("autoshape X").Visible = True

    Else

    ActiveSheet.Shapes("autoshape X").Visible = False

    End If

    If ActiveSheet.Range("T10").Value = "Rectangle" Then

    ActiveSheet.Shapes("Rectangle X").Visible = True

    Else

    ActiveSheet.Shapes("Rectangle X").Visible = False

    End If

    If ActiveSheet.Range("T10").Value = "Oval" Then

    ActiveSheet.Shapes("Oval X").Visible = True

    Else

    ActiveSheet.Shapes("Oval X").Visible = False

    End If

Next, double click on your combobox.  This will open a module in VBA, with an empty built in Sub called 'Private Sub ComboBox1_Change'.

Paste the macro into the area between Private Sub ComboBox and End Sub.

IMPORTANT:  Edit the 'X' in ALL of the autoshape references above to the numbers that match your autoshapes.  If your triangle is Autoshape 2, make the X in BOTH places a 2.  Likewise for the other two.

Close back to Excel.  Click on the Design Mode button (top left) in the Control Toolbox to lock the combo box.  

Now, cycle through the selections in the Combo box and your autoshapes will be made visible by the selection in the Combo box.

For pictures the process is the same, just the shape names will be 'Picture 1', etc.

I hope this is somewhat close to what you had in mind.