VBA: Code or Program a Data Validation List
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