Excel - Creating drop-down lists

August 2017




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


Published by aakai1056. Latest update on March 5, 2012 at 05:36 AM by aakai1056.
This document, titled "Excel - Creating drop-down lists," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).