Get Data Validation Range

valid

In Add Data Validation to any worksheet using VBA I demonstrated a technique for … adding data validation to any range. Surprise!

Now we'll try and do something with those data validation ranges. What I'd like to do is select a cell that has data validation on it and return the list of valid members (assuming that the source data is a Range or name that refers to a Range).

For example, if cell A1 has the 'List'-style data validation applied to it, and the source data is H1:H10, I want to return the values in H1:H10.

data validation range

So I wrote a function that takes a Range Object as an argument and returns the source data for the Data Validation as a Range Object.

The GetValidationRange Function

This function isn't as robust as it could be. For example, if you choose the "List" option and enter a set of values directly into the dialog box, the function will fail because it's expecting a Range or a name that points to a Range. We'll work on another version in a bit. But here goes:

Function GetValidationRange(rng As Excel.Range) As Excel.Range

Dim currentValidation As Excel.Validation
Dim targetRange As Excel.Range
Dim validationType As Excel.XlDVType

  ' grab Validation object and type
  Set currentValidation = rng.validation
  ' check for no existing validation, or multiple validation criteria
  On Error Resume Next
    validationType = currentValidation.Type
    If Err.number <> 0 Then
      Exit Function
    End If
  On Error GoTo 0

  ' formulas only used in List and Custom types
  If (validationType = xlValidateList Or validationType = xlValidateCustom) Then

    ' test for range reference and not a list of values
    On Error Resume Next
    Set targetRange = Excel.Range(currentValidation.Formula1)
    On Error GoTo 0

    If Not targetRange Is Nothing Then
      Set GetValidationRange = targetRange
    End If
  End If

End Function

Sample usage

Now I can retrieve all possible valid values for a given data validation cell. Suppose I have a cell with data validation applied (cell A1) and it's linked to a chart on the worksheet. I want to cycle through all the values and print the newly updated chart each time the source data (the cell with data validation) changes.

Sub TestValidation()

Dim wkshtChart As Excel.Chart
Dim wksht As Excel.Worksheet
Dim rng As Excel.Range
Dim targetRange As Excel.Range
Dim arrData As Variant
Dim i As Long

  ' assume active sheet
  Set wksht = ActiveSheet
  ' chart to be printed, assume it's the only chart object on the sheet
  Set wkshtChart = wksht.ChartObjects(1).Chart
  ' validation cell linked to chart
  Set rng = wksht.Range("A1")
  ' get data validation range
  Set targetRange = GetValidationRange(rng)

  If targetRange Is Nothing Then
    MsgBox "could not get validation range"
    Exit Sub
  End If

  ' assign validation values to array
  ReDim arrData(1 To targetRange.count)
  arrData = Application.WorksheetFunction.Transpose(targetRange.value)

  ' put each array value into worksheet, chart will auto-update
  For i = 1 To targetRange.count
    rng.value = arrData(i)

    ' print chart
    wkshtChart.PrintOut

    ' pause 2 seconds to allow for printing
    Application.Wait (Now + TimeValue("0:00:02"))
  Next i

End Sub

Retrieve Data Validation Values

Instead of a Range Object, let's retrieve the values associated with a data validation range. The advantage of this function is that it will return the possible values available to a cell regardless of where those values come from — a range, a range name, a formula, or a literal list of values.

Because of this, the function is declared As Variant. If the type is "List" or "Custom" and the source is a formula or range name, the range values are put into a Variant.

Function GetValidationRange2(rng As Excel.Range) As Variant

Dim currentValidation As Excel.validation
Dim targetRange As Excel.Range
Dim validationType As Excel.XlDVType
Dim tempValues() As Variant

  ' grab Validation object and type
  Set currentValidation = rng.validation
  ' check for no existing validation, or multiple validation criteria
  On Error Resume Next
    validationType = currentValidation.Type
    If Err.number <> 0 Then
      Exit Function
    End If
  On Error GoTo 0

  ' formulas only used in List and Custom types
  If (validationType = xlValidateList Or validationType = xlValidateCustom) Then

    ' test for range reference
    On Error Resume Next
    Set targetRange = Excel.Range(currentValidation.Formula1)
    On Error GoTo 0

    ' get values from range, or directly from data validation dialog box
    If Not targetRange Is Nothing Then
      tempValues = WorksheetFunction.Transpose(targetRange.value)
    Else
      tempValues = Split(currentValidation.Formula1, ",")
    End If

    GetValidationRange2 = tempValues
  End If

End Function

The only difference here is that the values from the range, or the values typed directly into the source box, are put into the array.

Sample usage

Sub LoopThroughValidation2()

Dim wkshtChart As Excel.Chart
Dim wksht As Excel.Worksheet
Dim rng As Excel.Range
Dim dataValidationValues() As Variant
Dim i As Long

  Set wksht = ActiveSheet
  ' chart to be printed
  ' assume one embedded chart
  Set wkshtChart = wksht.ChartObjects(1).Chart
  ' validation cell
  Set rng = wksht.Range("A1")
  ' get possible cell values
  dataValidationValues = GetValidationRange2(rng)

  For i = LBound(dataValidationValues) To UBound(dataValidationValues)
    rng.value = dataValidationValues(i)

    ' print chart
    With wkshtChart
      .PrintOut
    End With

    ' pause 2 seconds to allow for printing
    Application.Wait (Now + TimeValue("0:00:02"))
  Next i
End Sub

Download sample workbook with all of these functions and procedures.