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

0 votes
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.
Sep 24, 2022 in Others by Kithuzzz
• 38,010 points
711 views

1 answer to this question.

0 votes

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.

enter image description here

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
answered Sep 25, 2022 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,670 points
696 views
0 votes
1 answer

How to create a Custom Dialog box in android?

Here I have created a simple Dialog, ...READ MORE

answered Feb 18, 2022 in Others by Rahul
• 9,670 points
674 views
0 votes
1 answer

How to add Conditional Formatting in Excel for a Range of Values

Three distinct rules are required, one for ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,420 points
754 views
0 votes
1 answer

How to create a dependent drop down list using [Apache POI]

There is nothing apache poi cannot do, ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,420 points
4,612 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
875 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,181 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
479 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
730 views
0 votes
1 answer

How to create a drop-down list in Excel?

Making a list of the items you ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 63,420 points
422 views
0 votes
1 answer

How to create a bar graph in Excel 2010 by counts?

Read on usage of pivot charts: Procedure: Pivot Table: Select Data Insert ...READ MORE

answered Oct 21, 2022 in Others by narikkadan
• 63,420 points
450 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP