+- QP School (https://qomplainerzschool.lima-city.de)
+-- Forum: Tutorials (https://qomplainerzschool.lima-city.de/forumdisplay.php?fid=3)
+--- Forum: Excel VBA Tutorials (https://qomplainerzschool.lima-city.de/forumdisplay.php?fid=48)
+--- Thread: Add data validation to a cell with VBA (/showthread.php?tid=5251)
Add data validation to a cell with VBA - Qomplainerz - 07-27-2023
Example:
Sub AddDataValidation()
With Range("A1").Validation
.Delete 'Clear any existing validation
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=1, Formula2:=100
.ErrorMessage = "Enter a whole number between 1 and 100."
.IgnoreBlank = True
.InCellDropdown = True
End With
End Sub
Explanation:
The code defines a subroutine named AddDataValidation.
The With statement is used to work with the data validation settings for cell A1.
The existing validation (if any) is cleared using the Delete method.
The Add method is used to add new data validation settings to the cell.
Type:=xlValidateWholeNumber specifies that only whole numbers are allowed.
AlertStyle:=xlValidAlertStop sets the alert style to stop, which displays an error message when an invalid value is entered.
Operator:=xlBetween indicates that the data should be between two values.
Formula1:=1 and Formula2:=100 set the lower and upper bounds for the data validation.
The ErrorMessage property is used to set the error message that appears when an invalid value is entered.
IgnoreBlank = True allows blank cells without triggering an error.
InCellDropdown = True enables the drop-down arrow in the cell for easy data selection.