How to create a drop-down in excel with custom values

Question

In Excel, I want to create a drop-down menu with a few fixed values. The majority of examples use input from sheets for drop-down menus. Can somebody offer an Excel tip on how to insert "IF, AND, OR" inside the drop-down box in my case? I came across this example for the sheet's current values everywhere.

Answer 1

You can accomplish that using code rather than VBA. The simplest method for a set list is to select Data Validation from the Data tab. Next, select "List" as your validation criteria under "Settings" and enter your three values (comma separated). By copying and pasting the formatting or by left-clicking and dragging the formatting, you can copy this to any cell.

If you really want to do it in VBA

Using the array

Sub CreateDropdownList()
    ' replace "A5:A12" with your named range if you have one
    Range("A5:A12").Select  ' range where you've listed your choices
    ' now sort them alphabetically, replace sheet1 with your sheetname
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A5"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        ' you can use your named range here as well
        .SetRange Range("A5:A12")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With        
    ActiveWorkbook.Names.Add Name:="choices", RefersToR1C1:= _
        "=Sheet2!R5C1:R12C1"
    Range("G13").Select  'this is the cell you want the dropdown in
    With Selection.Validation
        .Delete
        'without array
        '.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        'xlBetween, Formula1:="IF, AND, OR"
        'with array
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=choices"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputMessage = "Select a value"
        .ErrorMessage = "No value selected"
        .ShowInput = True
        .ShowError = True
    End With
End Sub