Create data validation lists from a range in Excel VBA

This script allows you to select a data range to be used as a source for a drop-down list, and then apply this validation to a specific range of cells.

Objective

• Use an existing data range to create a drop-down list (data validation) in another range.
• Apply data validation to a defined range of cells.

Detailed VBA Code

Sub CreateValidationList()
    Dim SourceRange As Range
    Dim DestRange As Range   
    ' Define the source range (the data to be used for the drop-down list)
    ' Example: A1:A10 contains the values for data validation
    Set SourceRange = Range("A1:A10")   
    ' Define the destination range (the cells where you want to apply the validation)
    ' Example: B1:B10 is the range where you want to apply the data validation
    Set DestRange = Range("B1:B10")   
    ' Delete any existing validations in the destination range
    DestRange.Validation.Delete   
    ' Apply the data validation with the source range
    With DestRange.Validation
        .Delete ' Remove any existing validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=" & SourceRange.Address(, , , True)
        .IgnoreBlank = True
        .InCellDropdown = True ' Creates a drop-down list in the cell
        .ShowInput = True
        .ShowError = True
    End With  
    ' Optional: Show a confirmation message
    MsgBox "Data validation created from " & SourceRange.Address, vbInformation
End Sub

Explanation of the Code

• Define the Ranges:
  • SourceRange: This is the range of cells containing the data that will be used for the drop-down list. In this example, it is Range(« A1:A10 »), meaning the cells A1 to A10 will be used as the source.
  • DestRange: This is the range of cells where you want to apply the data validation. In this case, it’s Range(« B1:B10 »), meaning the cells B1 to B10 will have the data validation applied.
• Delete Previous Validations:
  • DestRange.Validation.Delete is used to delete any existing data validation in the destination range. This ensures there are no validation conflicts.
• Apply Data Validation:
  • The block With DestRange.Validation is used to define and apply the data validation to the DestRange cells.
  • .Add is used to set the validation type. Here, we specify that we are using a list (xlValidateList), and the source for the list is from SourceRange. The formula Formula1:= »= » & SourceRange.Address(, , , True) refers to the source range dynamically.
  • .InCellDropdown = True creates the drop-down list in the destination cells.
• Confirmation Message:
  • An optional message box appears to confirm that data validation has been created from the source range. This can be useful for the user.

Customizing the Code

• Change the Source Range: You can modify the Range(« A1:A10 ») to point to the range that contains your desired values for the drop-down list.
• Change the Destination Range: Modify Range(« B1:B10 ») to apply the validation to a different range of cells.
• Add Additional Features: You can extend the script to add custom input or error messages using .InputMessage or .ErrorMessage.

Example Use Case

If you have a list of categories in cells A1 to A10 (like « Fruit », « Vegetable », « Meat », etc.), this code will create a drop-down list in cells B1 to B10 with those values, so users can select a category from the list.

Conclusion

This VBA script allows you to easily create data validation lists in Excel based on a specific data range. It’s fully customizable to suit your needs and can be extended to include additional features such as input or error messages.