none
Excel 2007 Macro - set Data Validation List Source with VBA

    Question

  • Using Excel 2007 with Macros enabled, how do you change the Source of a Data Validation List using VBA?  I am not trying to set the value of the source to a sheet cell range but rather to a list of value using VBA like Source = "test1,test2,test3".  Does anyone know how this can easily be done using VBA because I was going to try to set the values of the list from a database result set.  Also, if it is possible, what is the maximum number of values that can be set as the source?

    Thanks.

    Monday, December 13, 2010 9:58 PM

Answers

  • Probably better to dump the list into cells somewhere, hidden perhaps. However you could try somehting like this -

    Sub test()
    Dim i As Long, n
    Dim sList As String
    Dim dv As Validation
        n = 32
       For i = 1 To n
           sList = sList & "test" & Right$("00" & i, 3)
           If i < n Then sList = sList & ","
       Next
        Set dv = Range("B2").Validation
       dv.Delete
       dv.Add xlValidateList, xlValidAlertStop, xlBetween, sList
    
    Debug.Print Len(sList)
    
    End Sub

    what is the maximum number of values that can be set as the source?

    Limit is 255 characters in the string, including commas Peter Thornton

    • Marked as answer by Bessie Zhao Tuesday, December 21, 2010 9:37 AM
    Monday, December 13, 2010 10:41 PM

All replies

  • Probably better to dump the list into cells somewhere, hidden perhaps. However you could try somehting like this -

    Sub test()
    Dim i As Long, n
    Dim sList As String
    Dim dv As Validation
        n = 32
       For i = 1 To n
           sList = sList & "test" & Right$("00" & i, 3)
           If i < n Then sList = sList & ","
       Next
        Set dv = Range("B2").Validation
       dv.Delete
       dv.Add xlValidateList, xlValidAlertStop, xlBetween, sList
    
    Debug.Print Len(sList)
    
    End Sub

    what is the maximum number of values that can be set as the source?

    Limit is 255 characters in the string, including commas Peter Thornton

    • Marked as answer by Bessie Zhao Tuesday, December 21, 2010 9:37 AM
    Monday, December 13, 2010 10:41 PM
  • This is very useful and gives me pretty much everything I need.

    One more quick question though:
    If you have a list already set as the ValidationList, is there anyway to be able to ouput the list prior to deleting it so I can so what it was before and then set the new value after?

    Thanks Peter for all of your help.

    Monday, December 13, 2010 10:59 PM
  • ' will error if no DV sList = Range("B2").Validation.Formula1
    ' MsgBox sList
    arr = Split(sList, ",")
    Range("C2").Resize(UBound(arr) + 1).Value = Application.Transpose(arr)

    Peter Thornton

    Monday, December 13, 2010 11:48 PM