Tom’s Tutorials for Excel: Data Validation from Custom List

Tom’s Tutorials for Excel: Data Validation from Custom List
A special solution that Tom has in his ‘Most Wanted’ file.

Data Validation does not directly access Custom Lists, but with VBA you can establish Data Validation in a cell, with the list of drop-down items being that of one of your Custom Lists.

Custom Lists are identifiable by their index number. In my collection of Custom Lists on my computer, the fifth one contains the names of my immediate family members. This macro example places Data Validation in cell C5, with the list of allowable items being the items in my Custom List #5.

The restriction I see, which you’d run into in any case, is having the total number of characters in your list being more than the maximum allowed by manual entry in the DV dialog’s List field. If that happens, you could write the list to a range, name it programmatically and in the macro stick the named range in the DV dialog. With my 7 family member names, I was easily under the maximum allowable character count. This worked in all versions of Excel from 97 to 2010:

Sub CustomListDV()
Dim strCustom$, i%, myCustomList As Variant
‘The (5) refers to the custom list of interest being
‘the fifth custom list in Excel on my computer.
myCustomList = Application.GetCustomListContents(5)
For i = LBound(myCustomList) To UBound(myCustomList)
strCustom = strCustom & myCustomList(i) & “,”
Next i
strCustom = Mid(strCustom, 1, Len(strCustom) – 1)
With Range(“C5”).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=strCustom
.ErrorTitle = “Invalid entry !”
.ErrorMessage = “Please enter an item” & Chr(10) & “from the drop-down list.”
.ShowError = True
End With
End Sub