How to create drop down list using data validation to display shapes in excel with a macro.
First we create a list of shapes names and the corresponding shapes as shown in the image below:
Next we create a drop down list using data validation as shown in the images below:
We can now create a named range called ‘MyShapes’ as described below.
Click on Formulas Tab (see image below) and select ‘Define Name’. In the New Name dialog box enter ‘MyShapes’ and in the ‘Refers to’ box write the formula below and click on ‘OK’.
The formula can be broken down as follws:
Sheet1!$A$2:$B$4 – This range refers to the names and shapes or pictures that we have created.
Sheet1!$F$2 – This cell or range in the worksheet refers to the drop down list.
Sheet1!$A$2:$A$4 – Based on the values in this range, we have created the drop down list in cell F2 using data validation.
So we are basically using a nested function with Index and Match.
The INDEX function can be used to find the value in the intersecting cell where a row and a column meet.
The following formula would give us the value in row 2 and column 2.
In place of row 2 in the above formula we use the Match function which gets us the row automatically:
The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. For example, if the range A2:A4 contains the values Triangle, Circle and Rectangle, then the formula =MATCH(“Circle”,A2:A4,0) returns the number 2, because ‘circle’ is the second item in the range.
Since we know that we want to get the data from column 2 from our created data range into the cell next to the drop down list. the nested function of Index and Match does its trick.
The image below shows the creation of the formula:
The next step is to select a shape, for example, the ‘circle’ from the drop down list. Now we copy the ‘circle’ shape and paste it next to the selected ‘circle’ from the drop-down list as a picture:
With the ‘circle’ shape selected we enter ‘=MyShapes’ next to the formula bar and press enter. Now if we select the shape ‘rectangle’ or ‘triangle’, the relevant shape is displayed.
Displaying images or pictures is easier using the process because we can do a simple copy paste of the pictures. This can be quite useful in displaying relevant pictures of employees next to their names.
Can we automate the process? Yes. We can record the steps of the creation of the drop-down list and the copying-pasting of the image and assigning it to the named range. Then we can tweak the recorded macro to create the automation. The complete macro or VBA code is given below:
‘ CreateDropDownListWithSDhapes Macro
‘ How To Create Drop Down List With Shapes In Excel
Range("F2").Clear Range("F2").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=$A$2:$A$4" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("F2") = "Circle" ActiveSheet.Shapes.Range(Array("Oval 9")).Select Selection.Copy Range("G2").Select ActiveSheet.Pictures.Paste.Select Selection.ShapeRange.IncrementLeft 8.25 Selection.ShapeRange.IncrementTop 9.75 Selection.Formula = "=MyShapes" Selection.ShapeRange.ScaleWidth 1.208488429, msoFalse, msoScaleFromTopLeft Selection.ShapeRange.ScaleHeight 1.208488429, msoFalse, msoScaleFromTopLeft Selection.ShapeRange.IncrementLeft -6.75 Selection.ShapeRange.IncrementTop -7.5 Range("F2").Select
Set myObj = ActiveSheet.DrawingObjects
For Each Pictur In myObj
If Left(Pictur.Name, 6) = “Pictur” Then
Watch the video tutorial.