How to make a drop down menu based on another one.

double

drop3

Drop down menu

In this article I’m going to show how to add a second drop-down menu  based on the value of the first one. There’s two ways to add a drop-down menu in Excel, by using Data Validation or by using a Combo Box.

  1. Using data validation
  2. Using combo box

1. Using data validation

To create a drop-down menu in Excel:

  1. Select a cell and go to tab Data -> Data Validation.
  2. Select “List” from Allow.
  3. Check “in-cell dropdown”.
  4. Select a range or named range as source for list and click OK.

In my example the first menu will list some car brands. Select the brands (B2:E2) and name the range “Brands“. To name a range write the name in the box to the left of the Function field.

input

Named range for the menu cells: H2 as “menu1” and H4 as “menu2“.

Select cell H2 and follow the steps above to add Data Validation.  Write =Brands in the source box for the list.

Name these ranges: B3:B7 is “Honda“, C3:C13 is “Toyota” and so on… (try with your own list and name the ranges accordingly).

For the next step we have to use a change listener for the sheet in order to decide what brand to use as source for menu 2.

Open VBA Editor and paste to the worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)

'Do nothing if more than one cell is changed or content deleted
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

If Target.Address = Range("menu1").Address Then
    
    Select Case Range("menu1")
        Case "Honda"
            With Range("menu2").Validation
                .Delete
                .Add Type:=xlValidateList, Formula1:="=Honda"
            End With
            Range("menu2").Value = Range("Honda").Cells(1) 'display 1st item in list
        Case "Toyota"
            With Range("menu2").Validation
                .Delete
                .Add Type:=xlValidateList, Formula1:="=Toyota"
            End With
            Range("menu2").Value = Range("Toyota").Cells(1)
        Case "Audi"
            With Range("menu2").Validation
                .Delete
                .Add Type:=xlValidateList, Formula1:="=Audi"
            End With
            Range("menu2").Value = Range("Audi").Cells(1)
        Case "Tesla"
            With Range("menu2").Validation
                .Delete
                .Add Type:=xlValidateList, Formula1:="=Tesla"
            End With
            Range("menu2").Value = Range("Tesla").Cells(1)
        Case Else
    End Select
    
End If

End Sub

Update:

Since the name of the selected brand in “menu1” is equal to the name of the associated list, the “select”-statement can be replaced by this:

Dim r As String
r = Range(“menu1”).Value
With Range(“menu2″).Validation
.Delete
.Add Type:=xlValidateList, Formula1:=”=” & r
End With
Range(“menu2”).Value = Range(r).Cells(1)

2. Using Combo Boxes

In this example we will use the first box’s method to trigger a change in the second one. Start by going to the Developer tab and insert two ComboBoxes from “Form Controls”.  I named them “Drop1” and “Drop2“.

With the first box, go to Format Control->Control and set the input range that shows the brands…the range must be a vertical list (J2:J5). Set cell link to “O2“, it’s here the chosen index will appear. Now it looks like this:

drop2

Paste the code below into a module and assign the macro to the first box:

Sub dropDownMenu()

Application.ScreenUpdating = False

Dim rng As Range
Set rng = Selection

Select Case Range("O2")
    Case 1 'Honda
        ActiveSheet.Shapes("Drop2").Select
        With Selection
            .ListFillRange = Range("honda").Address ' or "$B$3:$B$7"
            .LinkedCell = "$O$4"
            .DropDownLines = 8
            .Display3DShading = True
            .ListIndex = 1
        End With
    Case 2
        ActiveSheet.Shapes("Drop2").Select
        With Selection
            .ListFillRange = Range("toyota").Address
            .LinkedCell = "$O$4"
            .DropDownLines = 8
            .Display3DShading = True
            .ListIndex = 1
        End With
    Case 3
        ActiveSheet.Shapes("Drop2").Select
        With Selection
            .ListFillRange = Range("audi").Address
            .LinkedCell = "$O$4"
            .DropDownLines = 8
            .Display3DShading = True
            .ListIndex = 1
        End With
    Case 4
        ActiveSheet.Shapes("Drop2").Select
        With Selection
            .ListFillRange = Range("tesla").Address
            .LinkedCell = "$O$4"
            .DropDownLines = 8
            .Display3DShading = True
            .ListIndex = 1
        End With
    Case Else
End Select

rng.Select
Application.ScreenUpdating = True

End Sub

For some reason the shape had to be selected, so that’s why I added the rng to restore the selection to the user.

UPDATE:

Replacing the “Select”-statement. Also referring to the ComboBox without selecting it. Funny how ComboBoxes in Form Controls are named DropDowns in VBA.

Sub dropDownMenu()

Application.ScreenUpdating = False

Dim i As Integer
Dim brand As String
i = Range("O2").Value 'index in the list of brands
i = i + 1 'since the list of brands starts on row 2
brand = Cells(i, 10).Value '10 is column J

With ActiveSheet.DropDowns("Drop2")
    .ListFillRange = Range(brand).Address
    .LinkedCell = "$O$4"
    .DropDownLines = 8
    .Display3DShading = True
    .ListIndex = 1
End With

Application.ScreenUpdating = True

End Sub
Advertisements