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 “”
Dim Choices As String
Choices = "1. Choice1, 2. Choice2, 3. Choice3"
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, Operator:= _
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True