Add Data Validation to any worksheet using VBA

checkmark

This article will show you a simple VBA procedure for setting up Data Validation on any worksheet.

For an introduction to Data Validation, see Debra Dalgleish's site.

This procedure takes all the arguments you'll need to set up Data Validation as if you did it manually. There are a LOT of arguments, but I felt it was better to have total control than to assume. Feel free to change this, for example, if you want to assume that you always want the dropdown arrows.

Sub AddValidation(targetRange As Excel.Range, validationType As XlDVType, _
                       AlertStyle As XlDVAlertStyle, Optional shwErr As Boolean = True, _
                       Optional errMsg As String, Optional errTitle As String, _
                       Optional shwinp As Boolean = False, Optional inpmsg As String, _
                       Optional inptitle As String, Optional igblank As Boolean = True, _
                       Optional dropdown As Boolean = True, _
                       Optional Operator As XlFormatConditionOperator, _
                       Optional Formula1 As Variant, Optional Formula2 As Variant)

Dim currentValidation As Excel.Validation
Dim op As XlFormatConditionOperator

  ' grab Validation object
  Set currentValidation = targetRange.Validation

  ' if validation type is "List" or "Custom", operator must be "Between"
  If (validationType = xlValidateList Or validationType = xlValidateCustom) Then
    op = xlBetween
  Else
    op = Operator
  End If

  ' if operator is "between" or "not between" (and not List or Custom),
  ' Formula2 must be specified
  If (op = xlNotBetween Or op = xlBetween) Then
    If (validationType <> xlValidateList And validationType <> xlValidateCustom) Then
      If IsMissing(Formula2) Then
        MsgBox "Formula2 must be specified if operator is 'Between' or " & _
    "'Not Between' and type is not 'List' or 'Custom'."
        Exit Sub
      End If
    End If
  End If

  ' delete existing, if any
  currentValidation.Delete

  ' create new
  With currentValidation
    .Add validationType, AlertStyle, op, Formula1, Formula2

    ' error messages
    .ShowError = shwErr
    .ErrorMessage = IIf(shwErr, errMsg, "")
    .ErrorTitle = IIf(shwErr, errTitle, "")

    ' input messages
    .ShowInput = shwinp
    .InputMessage = IIf(shwinp, inpmsg, "")
    .InputTitle = IIf(shwinp, inptitle, "")

    .IgnoreBlank = igblank
    .InCellDropdown = dropdown
  End With

End Sub

This procedure accepts the following arguments:

The range you want to add Data Validation,
the type of validation you want (a built-in constant),
the alert style (another built-in constant),
whether you want to show a msgbox for errors,
the text of the error message,
the title of the error message box,
whether you want to show a msgbox when entering a Data Validation cell,
the text of the input message,
the title of the input message box,
whether you want to ignore blank cells,
whether you want to show the dropdown arrows when you select a Data Validation cell,
the operator to use (yet another built-in constant), and finally
two Variants that hold the formula or values you want to be part of the validation.

The first thing the procedure does is grab the relevant Range.Validation Object. Then we check the validation type. If it is "List" or "Custom", the Operator parameter must be "Between." You can see this in the Data Validation box; when you choose the type, if it is "List" or "Custom", the Operator box changes to "Between" and you can't change it:

Data Validation

If the validation type is not "List" or "Custom", and the Operator is "Between" or "Not Between", then the Formula2 parameter must be specified (because these are the only two operators that require a range of values).

Once those checks are made, the existing validation is cleared (Validation.Delete). Then we add a new Validation Object using the parameters specified when the procedure was called.

Sample usage

Sub TestAddValidation()

Dim rng As Excel.Range

  Set rng = Range("A1:A100")

  AddValidation rng, xlValidateList, _
    xlValidAlertStop, , "Don't enter bad numbers!", "Bad number", , , , , , xlBetween, "=My_Data_Range"

End Sub

September 24, 2010 -- Protect Excel sheets while allowing data entry
January 15, 2010 -- Another take on Data Validation

2 Comments:

rick writes:
January 26, 2012 at 1:33 pm
Hello JP
Can you show an example of using your AddValidation function with a string array as input for the resulting validation list? I would like to use your idea, combined with a DistinctiveValues function idea at this location. ( http://www.cpearson.com/excel/distinctvalues.aspx ) It returns a list of distinct values from a range. I can convert it output into a string array, then perhaps use that string array as an input to your function.
Thanks,
Rick
- JP writes:
  January 27, 2012 at 8:27 am
  I think one of the last two parameters would already work for this. You would need to join the array using a comma as the delimiter, then simply pass this string as the final argument to the procedure.
  In my example above, "=My_Data_Range" refers to a range containing a list of unique values. If you wanted the validation to be "Yes" or "No", you would put "Yes,No" in the Source box. So you would simply use "Yes,No" as the final parameter for the AddValidation procedure. If you had an array with "Yes" as the first element and "No" as the second element, joining them would create this string.
```
Dim arr As Variant
Dim arrayString As String
arr = Array("Yes", "No")
arrayString = Join(arr, ",")
```
  Now pass arrayString instead of "=My_Data_Range" and it should work.