Dynamically generated multi select drop down list in Excel

Question

Need to know if it is possible to create a multi select drop down list in excel which in turn dynamically links to another multi select drop down list.

For example, consider two columns namely Country and City as follows

India -> Delhi, Mumbai, Bangalore;
Australia -> Sydney, Hobart, Brisbane, Perth;
USA -> CA, NYC, LA;
Pakistan -> Lahore, Karachi, Peshawar

Suppose if i select India and Australia in my first drop down, i should have an option to select one or more options in Delhi, Mumbai, Bangalore, Sydney, Hobart, Brisbane and Perth only.

EDIT: I have been able to create a multi select drop down list and a single select drop down that dynamically updates according to the single value selected in the parent column. I am not able to combine both of them

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Oldvalue As String
    Dim Newvalue As String
    Dim Extract As String
'Dim arr As Variant
Dim i As Integer
Dim ComboList As String

Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Column = 8 Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue = "" Then
        Target.Value = Newvalue
      Else
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & ", " & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True

If Target.Column = 9 Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue = "" Then
        Target.Value = Newvalue
      Else
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & ", " & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
   End If


    TargetRow = ActiveCell.Row
    MsgBox (TargetRow)
    TargetCol = ActiveCell.Column

    Dim CLInd As String: CLInd = "Delhi, Mumbai, Bangalore"
    Dim CLAus As String: CLAus = "Sydney, Hobart, Perth, Brisbane"
    Dim CLPak As String: CLPak = "Lahore, Karachi, Peshawar"
    Dim CLNZ As String: CLNZ = "Auckland,Wellington"
    Dim CLUSA As String: CLUSA = "CA, LA, DC"


    Extract = Index(A2:J10, TargetRow - 1, 9)




    If InStr(1, Extract, "IND") <> 0 Then
       ComboList = ListJoin(ComboList, CLInd)
    End If

    If InStr(1, Extract, "AUS") <> 0 Then ComboList = ListJoin(ComboList, CLAus)
    End If

    If InStr(1, Extract, "PAK") <> 0 Then ComboList = ListJoin(ComboList, CLPak)
    End If

    If InStr(1, Extract, "NZ") <> 0 Then ComboList = ListJoin(ComboList, CLNZ)
    End If

    If InStr(1, Extract, "USA") <> 0 Then ComboList = ListJoin(ComboList, CLUSA)
    End If

  End If 'target column=9

  Call UpdateCombo(Target.Value, ComboList)  'target.value or target.range

End Sub

Private Function ListJoin(Str1 As String, Str2 As String) As String
    If Str2 = "" Then ListJoin = Str1
    If Str1 = "" And ListJoin = "" Then ListJoin = Str2
    If ListJoin = "" Then ListJoin = Str1 & "," & Str2
End Function

Private Function UpdateCombo(ByVal Target As Range, ComboList As String)
    With Target.Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=ComboList
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    If InStr(ComboList, Target.Value) = 0 Then Target.Value = ""
End Function

What kind of drop down list you expect? In windows form? What have you tried so far? Where is your current try? — Michał Turczyn, Jan 30 '18 at 07:23
Have a look in [Here](https://stackoverflow.com/questions/3014421/how-to-add-events-to-controls-created-at-runtime-in-excel-with-vba), it might help you out. BTW you should post code of what you tried so far and explain what it does and what it should do. — Miguel_Ryu, Jan 30 '18 at 09:43
Google for *dynamic dependent drop down list*. Here is an example: http://excelsemipro.com/2011/05/a-dynamic-dependent-drop-down-list-in-excel/. This example used tables which is probably a good idea. You could also do it using dynamic named ranges (using `OFFSET` and `COUNTA`) and `INDIRECT` — Dan, Jan 30 '18 at 10:35
Thanks for the suggestion @Dan. But, the whole point of the problem is to be able to select multiple values in a drop down list. That can be done easily using VBA but the problem comes when one has to update another list on the basis of the (multiple)selections in the first list. — Krishna Rathi, Jan 30 '18 at 12:37
@Krishna I think you need to rework this question to provide an [mcve](https://stackoverflow.com/help/mcve). If you are looking for a VBA solution, you should provide the VBA code you have so far — Dan, Jan 30 '18 at 14:01

score 0 · Accepted Answer · answered Jan 30 '18 at 19:27

0

The best place for multi select code is over at the Contextures website. See http://www.contextures.com/excel-data-validation-multiple.html

answered Jan 30 '18 at 19:27

jeffreyweir

4,668
1
16
27

Thankyou @jeffreyweir – Krishna Rathi Jan 31 '18 at 13:56
Cool. Please accept my answer If that solves your issue. – jeffreyweir Jan 31 '18 at 18:12

Dynamically generated multi select drop down list in Excel

1 Answers1