How to use Data Validation in Excel from VBA?
Data Validation
Data validation is MS Excel’s feature to allow you to create certain rules that prescribe what can be entered into a cell. For example, you want to enter a number 0 to 9 in a particular cell, then you need to create rules in data validation. If the user enters an invalid entry, the user gets a system error message but you can also display a custom message as shown below. Click here for how to use data validation in excel sheet from the dialog box
Here are given some example to Add Data Validation from VBA
How to use Data Validation in Excel from VBA?
How to add value into Data Validation from VBA?
In this code, we add list manually in programming.
Sub Data Validation()
With Selection.Validation ‘Applying Data validation in selecting cells
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=”Jan,Feb,Mar,Apr,May,Jun” ‘Manually add list here
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = “Message” ‘for show suggestion message to enter valid value into cell
.ErrorTitle = “Error” ‘for show error
.InputMessage = “Please Fill Right Value” ‘write custom message
.ErrorMessage = “Please Fill Right Value” ‘write custom message
.ShowInput = True
.ShowError = True
End With
End Sub
How to use Data Validation into Excel from VBA?
How to add value into Data Validation using a loop in VBA?
In this code, we add a validation list from the loop, but this code adds all duplicate.
Sub Data_Validation_Loop()
Dim Data As Range, Cells_Data As Range
Dim Validation_Val As String
Set Data = Range(“A2”, Range(“A1”).End(xlDown)) ‘Assign Range object as reference to an object variable
For Each Cells_Data In Data
Validation_Val = Validation_Val & “,” & Cells_Data ‘We compile Cells Data from loop in Validation_Val
Next Cells_Data
With Selection.Validation ‘Applying Data validation in selecting cells
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=Validation_Val ‘Add validation list
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = “Message” ‘for show suggetion message to enter valid value into cell
.ErrorTitle = “Error” ‘for show error
.InputMessage = “Please Fill Right Value” ‘write custom message
.ErrorMessage = “Please Fill Right Value” ‘write custom message
.ShowInput = True
.ShowError = True
End With
End Sub
How to use Data Validation into Excel from VBA?
How to add unique value to data validation from VBA Code?
From this code we add unique value into Data Validation
Sub Add_Unique_Value_To_Validation()
Dim Data As Variant
‘We use variant data type for all types of Data to add Validation list
Dim i As Integer
Dim Validation_Val As String
Data = Range(“A2”, Range(“A1”).End(xlDown)).Value
For i = LBound(Data, 1) To UBound(Data, 1)
If WorksheetFunction.CountIf(Range(“A2:A” & i + 1), Data(i, 1)) = 1 Then ‘Get Unique Value
Validation_Val = Data(i, 1) & “,” & Validation_Val ‘Comiple Unique Value
End If
Next i
With Range(“G2”).Validation ‘Applying Data validation in selecting cells
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=Validation_Val ‘Add validation list
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = “Message” ‘for show suggetion message to enter valid value into cell
.ErrorTitle = “Error” ‘for show error
.InputMessage = “Please Fill Right Value” ‘write custom message
.ErrorMessage = “Please Fill Right Value” ‘write custom message
.ShowInput = True
.ShowError = True
End With
End Sub