VBA: Code or Program a Data Validation List

August 14th, 2004 | Categories: VBA | Tags:
-->

Most users are familiar with restricting a cell’s input to a data validation list. There are times however where you would like this list to be dynamic, or you may need to program it. This code example will let you set the data validation list from a string variable.

Tip: Code or Program a Data Validation List

The following code places a custom data validation list in cell A1 creating the list from the variable Choices. You can change Input Title, Input Message, etc. by typing what you would like between the “”

Sub ProgramValidate()

    Dim Choices As String
    Choices = "1. Choice1, 2. Choice2, 3. Choice3"

    Range("A1").Select

        With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, _
        AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=Choices
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub
Can't get the tutorial to work for you? Need help with your code?
Get answers right away at our AE Excel Support Forums!
Leave a comment | Trackback
  1. Maku Maku
    August 7th, 2008 at 15:27
    Reply | Quote | #1

    Why doesn’t this work with just numbers?

    I want to chance the choices to:
    0, 3, 5 and 10

    Dim choices As Single

    choices = “0, 3, 5, 10″

    But this gives me an Mismatch error (13)

  2. Tom
    August 8th, 2008 at 11:31
    Reply | Quote | #2

    Hey Maku,

    Your way should work the same way, I just tried it. Make sure the “choices” is capital => “Choices”.

    Follow this:
    1) Click on the A1 box.
    2) Tools >>> Marcos >>> Visual Basic Editor
    3) Copy the VBA code, replace the choices with your numbers.
    4) File >>> Save
    5) Go back to A1, click ALT + F8, and select your marco.
    6) That’s it, you should have a dropdown box.

    Let me know how it went.

  3. Nate
    September 4th, 2008 at 19:41
    Reply | Quote | #3

    Thanks for the code. I modified it for my purposes, and it works fine except it is limited to only 33 list values. Is there a limit on how many items can show up in a cell drop down???

  4. Nejdet
    April 21st, 2009 at 18:22
    Reply | Quote | #4

    How do you add the line to what I am trying to write?

    Set objExcelApp1 = CreateObject(“Excel.Application”)

    objExcelApp1.Workbooks.Open “L:\DEPT1\D030B\Financial Compliance Team\State Reporting\Automated Form and Emailer\State of Maine 2008 Report Files\Me Template”, , False
    Set objExcelBook1 = objExcelApp1.ActiveWorkbook
    Set objExcelSheets1 = objExcelBook1.Worksheets
    Set objexcelsheet1 = objExcelBook1.Sheets(1)
    Set objSelection = objSelection.Validation
    objexcelsheet1.Activate

    Choices = “APRN, CRNA, CRNP, DDS, DO, DPM, DVM, MD, ND, NP, OD, PA, RN, Health Plans & Benefit Managers, Nursing Home Administrators, Pharmacists, Pharmacy Technicians, Psychologists, Veterinary Technicians, other”

    objexcelsheet1.Range(“a14″).Select
    objexcelsheet1.Range(“a14″).PasteSpecial

    objexcelsheet1.Rows(“14″).Select
    objexcelsheet1.Rows(“14″).Delete

    objexcelsheet1.Range(“r17:r2000″).Select

    With objSelection
    .Delete
    .Add Type:=xlValidateList, _
    AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=Choices
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = “”
    .ErrorTitle = “”
    .InputMessage = “”
    .ErrorMessage = “”
    .ShowInput = True
    .ShowError = True

    End With

  5. vanceza
    February 1st, 2010 at 16:29
    Reply | Quote | #5

    Nate:
    If you pass in a string for the list, it’s limited to 255 characters.

  6. Jescemine
    June 17th, 2010 at 13:45
    Reply | Quote | #6

    I’m trying to create a data validation list in an excel column from word VBA. I used that code. But getting the complie error Selection.Validation method not found.

    Please help.

  7. Bill Standke
    November 16th, 2010 at 13:50
    Reply | Quote | #7

    How do you handle this when you need to assign a calculated dynamic range for validation in vba. For example:
    =offset(folderstart,MATCH(TRIM(MID(O2,1,FIND(” = “,” & icell & “))),Presentation_Catalog,0)-1,1,countif(Presentation_Catalog,TRIM(MID(” & icell & “,1,FIND(” = “,” & icell & “)))),1)

    When I do this I get “False” as the validation value instead of the formula

  8. Sunith
    November 30th, 2010 at 06:44
    Reply | Quote | #8

    Hi,

    I wanna create an Excel Sheet, which should n Allow me to Enter duplicate values, I.e; If i had 2 columns with X in one column and Y in Another, The same Pattern of X and Y should be entered anywhere else in the Excel, And if it does, Excel Should Throw an Error message stating that “The Combination is already entered”. How do i create such an coding. Can any on you suggest me an idea, and a complete set of coding for this.

    Thanks in Advance

  9. Rico
    March 2nd, 2011 at 12:34
    Reply | Quote | #9

    Hello!

    Is there a way to put more than a 255 caracters ?

    Best regards,

  10. Anand
    March 16th, 2011 at 09:09
    Reply | Quote | #10

    Is it possible to add data into list by giving some range
    stored in a variable instead of storing data in variable choices ?