52 – Excel VBA Code to Set Data Validation | Excel Automation | #excelsteps

52 – Excel VBA Code to Set Data Validation | Excel Automation | #excelsteps
Download Link:
https://drive.google.com/drive/folders/1fVSxQOn3XVe-qazURxPkPZBgw7ytdfgw?usp=sharing

In VBA (Visual Basic for Applications), data validation is a feature that allows you to control the type and range of data that users can enter into cells in an Excel worksheet. It helps to ensure data integrity, restrict input to specific values or formats, and provide user-friendly prompts or error messages.

You can apply data validation to cells or ranges using the `Validation` property of the `Range` object. Here's how you can apply data validation in VBA:

1. Select the cell or range where you want to apply data validation.

2. Use the `Validation` property to access the data validation settings for the selected range.

3. Set the various properties of the `Validation` object to define the validation rules and settings.

Let's explore some important properties of the `Validation` object that you can use to configure data validation:

- `Type`: This property specifies the type of data validation to apply. It can take the following values:
- `xlValidateWholeNumber`: Restricts input to whole numbers.
- `xlValidateDecimal`: Restricts input to decimal numbers.
- `xlValidateDate`: Restricts input to dates.
- `xlValidateTextLength`: Restricts input based on the length of the text.
- `xlValidateList`: Restricts input to values from a predefined list.

- `Formula1` and `Formula2`: These properties are used to specify the validation criteria. The values depend on the `Type` property selected. For example:
- For `xlValidateWholeNumber` or `xlValidateDecimal`, you can set `Formula1` to a numeric expression or a range that defines the acceptable values or range of values.
- For `xlValidateDate`, you can set `Formula1` and `Formula2` to specify the range of acceptable dates.
- For `xlValidateTextLength`, you can set `Formula1` to specify the maximum allowed text length.
- For `xlValidateList`, you can set `Formula1` to a range or an array containing the list of values.

- `InputTitle` and `InputMessage`: These properties allow you to provide a title and a message that appear as a prompt when the user selects a cell with data validation.

- `ErrorMessage`: This property allows you to set an error message that is displayed if the user enters an invalid value.

- `ShowInput` and `ShowError`: These properties determine whether to show or hide the input and error messages.

Here's an example that demonstrates how to apply data validation in VBA:

```vba
Sub ApplyDataValidation()
Dim rng As Range
Set rng = Range("A1:A10") ' Range where data validation will be applied

With rng.Validation
.Delete ' Clear any existing data validation settings

' Apply data validation
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=1, Formula2:=100
.InputTitle = "Input"
.InputMessage = "Enter a whole number between 1 and 100."
.ErrorMessage = "Invalid input. Please enter a valid whole number."
.ShowInput = True
.ShowError = True
End With
End Sub
```

In this example, we apply data validation to the range `A1:A10` with the following settings:
- Type: `xlValidateWholeNumber` to restrict input to whole numbers.
- AlertStyle: `xlValidAlertStop` to display an error message and prevent the user from entering an invalid value.
- Operator: `xlBetween` to specify that the value should be between the values defined in `Formula1` and `Formula2