Excel - Creating drop-down lists

December 2016




Issue


I want to create dropdown lists in excel, it should work as follow: In cell2,1 must be a drop down list with 4 names (e.g. Mill, Comp, Loads, Blending), if I select say Mill then in cell2, 2 a dropdown list with all the mill parts must appear, same for the other if I select one of the other names their parts list must appear. Can anyone show me how to do this?

Solution


Try this:
  • On a new sheet (let say sheet2) in Cells A1, B1, C1 and D1 enter values Mill, Comp, Loads, Blending respectively
  • Put the values of Mill, Comp, Loads and Blending under its respective column heading (make sure no blank line)
  • Go to Formula Tab on menu
  • Select Cells A1:D1
  • Click on define Name
  • Enter the name, lets say "MyMainList" and click Ok
  • Select all the values in col A, starting from A2 and click on name, give it name "Mill"
  • Select all the values in col B, starting from B2 and click on name, give it name "Comp"
  • Select all the values in col C, starting from C2 and click on name, give it name "Loads"
  • Select all the values in col D, starting from D2 and click on name, give it name "Blending "
  • Go to main sheet (lets say sheet1) and select cell A2
  • Go to Data tab on menu and click on Data Validation
  • Click on the drop down titled "Allow" and choose List
  • In Source box write =MyMainList
  • Optionally go to input tab and error tab to enter messages for that
  • Press ALT+F11
  • Double click on "sheet1" in the project explorer
  • Paste code.


Private Sub Worksheet_Change(ByVal Target As Range) 


    If Target.Column <> 1 Then Exit Sub 
     
    Application.EnableEvents = False 
     
    Dim mainsrc As String 
    Dim subsrc As String 
     
    Dim lThisRow As Long 
    lThisRow = Target.Row 
     
    If Cells(lThisRow, 1) = "" Then 
         
        Cells(lThisRow, 2).Validation.Delete 
        GoTo exit_sub 
    End If 
     
    mainsrc = Cells(lThisRow, 1).Value 
     
    On Error Resume Next 
    subsrc = Cells(lThisRow, 2).Validation.Formula1 
    On Error GoTo 0 
     
    If ("=" & subsrc = mainsrc) Then 
        GoTo exit_sub 
    End If 
     
  Cells(lThisRow, 2) = "" 
  With Cells(lThisRow, 2).Validation 
        .Delete 
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=" & mainsrc 
        .IgnoreBlank = True 
        .InCellDropdown = True 
        .InputTitle = "This is my Input Title" 
        .ErrorTitle = "Oops Error" 
        .InputMessage = "Select a Value" 
        .ErrorMessage = "Not a valid value" 
        .ShowInput = True 
        .ShowError = True 
    End With 
     
     
exit_sub: 
     
    Application.EnableEvents = True 

End Sub

Note


Thanks to rizvisa1 for this tip on the forum.

Related :

This document entitled « Excel - Creating drop-down lists » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.