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 in Others by Kithuzzz
• 20,660 points
72 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 by narikkadan
• 37,660 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 in Others by Edureka
• 13,640 points
112 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 in Others by Rahul
• 9,680 points
185 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 in Others by narikkadan
• 37,660 points
45 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 in Others by narikkadan
• 37,660 points
262 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,720 points
454 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
2,227 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 in Others by gaurav
• 22,040 points
71 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 in Others by Edureka
• 13,640 points
210 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 in Others by narikkadan
• 37,660 points
65 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 in Others by narikkadan
• 37,660 points
44 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