How to add data validation to a cell using VBA

Question

I want to add "data validation" in a cell(which is variable) using VBA and the range which is to come in the data validation list is also variable. Till now I have been using this

Here "range1" is the range which is to come in the data validation list and "rng" is the cell where I want the data validation

Dim range1, rng As range
Set range1 = range("a1:a5")
Set rng = range("b1")
With rng
With .Validation
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="range1"
End With
End With

I am getting "application defined and object defined error"

Also can someone explain me the meaning of different arguments in

With .Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="range1"

Dmitry Pavliv · Accepted Answer · 2014-04-09 08:36:04Z

58

Use this one:

Dim ws As Worksheet
Dim range1 As Range, rng As Range
'change Sheet1 to suit
Set ws = ThisWorkbook.Worksheets("Sheet1")

Set range1 = ws.Range("A1:A5")
Set rng = ws.Range("B1")

With rng.Validation
    .Delete 'delete previous validation
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Formula1:="='" & ws.Name & "'!" & range1.Address
End With

Note that when you're using Dim range1, rng As range, only rng has type of Range, but range1 is Variant. That's why I'm using Dim range1 As Range, rng As Range.
About meaning of parameters you can read is MSDN, but in short:

Type:=xlValidateList means validation type, in that case you should select value from list
AlertStyle:=xlValidAlertStop specifies the icon used in message boxes displayed during validation. If user enters any value out of list, he/she would get error message.
in your original code, Operator:= xlBetween is odd. It can be used only if two formulas are provided for validation.
Formula1:="='" & ws.Name & "'!" & range1.Address for list data validation provides address of list with values (in format =Sheet!A1:A5)

edited Apr 9, 2014 at 8:36

answered Apr 9, 2014 at 8:30

Dmitry Pavliv

35.9k13 gold badges84 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Gajju Over a year ago

Hey I did the changes and its working just fine, thanks for help<br/> Hey can you please explain <br/>' <Formula1:="='" & ws.Name & "'!" & range1.Address> '<br/> again in a little detail, I just don't undersatnd what is "='" and "=!" ,I am new to VBA, please don't mind or if you have any link where I can study it in detail will be awesome.

Dmitry Pavliv Over a year ago

@Gajju, no worry, it's easy:) lets start! desired result is something like this: Formula1:="='Sheet1'!A1:A5" - All formulas in excel starts from equal sign: =. Next, 'Sheet1'! means that range A1:A5 belongs to sheet Sheet1 (if your sheet name doesn't contain spaces, you can ommit sigh ' like this: Formula1:="=Sheet1!A1:A5". But we don't want to hardcode this formula, so we use: Formula1:="='" & ws.Name & "'!" & range1.Address where ws.Name returns sheet name (e.g. "Sheet1") and range1.Address returns A1:A5.

Gajju Over a year ago

Hey it was easy to understand, thanks for the time and thanks again for the help...

Vito Gentile Over a year ago

A quick addition to this great answer: in case you want to add a list of values which is not a range of cells, you must specify a list of comma-separated values as Formula1 parameter. For instance, if you want to constrain the content of the cell to contain one of the letters A, B or C, the code to be used is Formula1:="A,B,C"

dondapati Over a year ago

@DmitryPavliv.how to apply the same operation to sequence of rows.i.e, row b1,b2,b3....Thankyou.

n a · Accepted Answer · 2023-04-20 12:21:43Z

If you get confused with setting the options of data validation, I will suggest "recording" a macro and diving into the codes there. For example, I just complete a sub to configure data validation for cells in a batch. Find the requirements and codes below.

Sub setCellDataValidation(idxRow As LongLong)
' column B
With Cells(idxRow, 2).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="E,PE,DE"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = "Input Format"
    .InputMessage = "E/PE/DE"
    .ErrorMessage = "Only E, PE, and DE allowed."
    .IMEMode = xlIMEModeNoControl
    .ShowInput = True
    .ShowError = True
End With
' column D
With Cells(idxRow, 4).Validation
    .Delete
    .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
    Operator:=xlBetween, Formula1:="0", Formula2:="100"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = "Input Format"
    .InputMessage = "Integer in 0 - 100"
    .ErrorMessage = "Only Integer in 0 - 100 allowed."
    .IMEMode = xlIMEModeNoControl
    .ShowInput = True
    .ShowError = True
End With
' column E
With Cells(idxRow, 5).Validation
    .Delete
    .Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="1/1/2022", Formula2:="12/31/2024"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = "Input Format"
    .InputMessage = "yyyy-mm-dd in 2022/1/1 to 2024/12/31"
    .ErrorMessage = "Only yyyy-mm-dd in 2022/1/1 to 2024/12/31 allowed."
    .IMEMode = xlIMEModeNoControl
    .ShowInput = True
    .ShowError = True
End With
' column F
With Cells(idxRow, 6).Validation
    .Delete
    .Add Type:=xlValidateTime, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="00:00:00", Formula2:="23:59:59"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = "Time Format"
    .InputMessage = "hh:mm in 00:00:00 to 23:59:59"
    .ErrorMessage = "Only hh:mm in 00:00:00 to 23:59:59 allowed."
    .IMEMode = xlIMEModeNoControl
    .ShowInput = True
    .ShowError = True
End With
' column W
With Cells(idxRow, 23).Validation
    .Delete
    .Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, Operator _
    :=xlBetween, Formula1:="0.01", Formula2:="2000.99"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = "Input Format"
    .InputMessage = "3 decimal"
    .ErrorMessage = "Only 3 decimal allowed."
    .IMEMode = xlIMEModeNoControl
    .ShowInput = True
    .ShowError = True
End With
End Sub

Collectives™ on Stack Overflow

How to add data validation to a cell using VBA

2 Answers 2

5 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Linked

Related