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

how to apply data validation in excel vba

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

How to use Data Validation in Excel from VBA with Example?

Download Workbook

Leave a Reply

Your email address will not be published. Required fields are marked *