Search Contextures Sites
Custom Search

Excel -- Data Validation -- Documentation

Use these sample macros to create a list of all the data validation on an Excel worksheet

Document a Worksheet's Data Validation: Text File

Document a Worksheet's Data Validation: Worksheet

Download the sample file

More Tutorials


Document a Worksheet's Data Validation: Text File

The following procedure creates a text file with a list of the active worksheet's data validation. For example:

 

A11

Whole Number

Less Than or Equal to 3

 

D4

List

Yes,No

 

D5

List

=DaysList

 

D8

Text Length

Less Than 5

 

D11

Custom

=AND($A$1<>"",$A$3<>"")

Thanks to J.E. McGimpsey for generously sharing his code.

Sub DataValDocumenter()
  'adapted from code posted by J.E. McGimpsey 2005-02-03
  'http://www.mcgimpsey.com/excel/index.html
  Dim sVal(0 To 2) As Variant
  Dim rValidation As Range
  Dim rCell As Range
  Dim nFile As Long
  Dim sC As String
  Dim strDV As String
  sC = vbTab
  On Error Resume Next
  Set rValidation = Cells.SpecialCells(xlCellTypeAllValidation)
  On Error GoTo 0
  If Not rValidation Is Nothing Then
    nFile = FreeFile
    Open "test.txt" For Output As #nFile
    For Each rCell In rValidation
      With rCell.Validation
        sVal(0) = Choose(.Type + 1, "Input Only", _
            "Whole Number", "Decimal", "List", "Date", _
            "Time", "Text Length", "Custom")
        sVal(1) = .Formula1
        sVal(2) = .Formula2

        Select Case .Type
          Case xlValidateWholeNumber, xlValidateDecimal, _
           xlValidateDate, xlValidateTime, xlValidateCustom
            Select Case .Operator
              Case xlAnd
                strDV = "Between" & sC & sVal(1) & sC _
                    & "And" & sC & sVal(2)
              Case xlNotBetween
                strDV = "Not Between" & sC & sVal(1) _
                     & sC & "And" & sC & sVal(2)
              Case xlEqual
                strDV = "Equal to" & sC & sVal(1)
              Case xlNotEqual
                strDV = "Not Equal to" & sC & sVal(1)
              Case xlGreater
                strDV = "Greater Than" & sC & sVal(1)
              Case xlLess
                strDV = "Less Than" & sC & sVal(1)
              Case xlGreaterEqual
                strDV = "Greater Than or Equal to" _
                     & sC & sVal(1)
              Case xlLessEqual
                strDV = "Less Than or Equal to" _
                     & sC & sVal(1)
              Case Else
                'do nothing
            End Select
          Case Else
            strDV = sVal(1)
        End Select
       End With
       strDV = sC & sVal(0) & sC & strDV
       Print #nFile, rCell.Address(False, False) & strDV
       Erase sVal
     Next rCell
     Close #nFile
  End If

End Sub 


Document a Worksheet's Data Validation: Worksheet

The following procedure creates a new worksheet in the active Excel file, with a list of the active worksheet's data validation.

Sub DataValDocumenterSheet()
  'adapted from code posted by J.E. McGimpsey 2005-02-03
  'http://www.mcgimpsey.com/excel/index.html
  ' downloaded from www.contextures.com
  Dim sVal(0 To 2) As Variant
  Dim rValidation As Range
  Dim rCell As Range
  Dim ws As Worksheet
  Dim lRow As Long
'  Dim nFile As Long
  Dim sC As String
  Dim strDV As String
'  sC = vbTab
  On Error Resume Next
  Set rValidation = Cells.SpecialCells(xlCellTypeAllValidation)
  On Error GoTo 0
    
  If Not rValidation Is Nothing Then
    Set ws = Worksheets.Add(Before:=Sheets(1))
    With ws
        .Range("A1:C1").Value = Array("Cell", "DV Type", "Formula")
        lRow = 2
        For Each rCell In rValidation
          With rCell.Validation
            sVal(0) = Choose(.Type + 1, "Input Only", _
                "Whole Number", "Decimal", "List", "Date", _
                "Time", "Text Length", "Custom")
            sVal(1) = .Formula1
            sVal(2) = .Formula2
    
            Select Case .Type
              Case xlValidateWholeNumber, xlValidateDecimal, _
                xlValidateDate, xlValidateTime, xlValidateCustom
                Select Case .Operator
                  Case xlAnd
                    strDV = "Between" & sC & sVal(1) _
                      & sC & "And" & sC & sVal(2)
                  Case xlNotBetween
                    strDV = "Not Between" & sC & sVal(1) _
                      & sC & "And" & sC & sVal(2)
                  Case xlEqual
                    strDV = "Equal to" & sC & sVal(1)
                  Case xlNotEqual
                    strDV = "Not Equal to" & sC & sVal(1)
                  Case xlGreater
                    strDV = "Greater Than" & sC & sVal(1)
                  Case xlLess
                    strDV = "Less Than" & sC & sVal(1)
                  Case xlGreaterEqual
                    strDV = "Greater Than or Equal to" _
                      & sC & sVal(1)
                  Case xlLessEqual
                    strDV = "Less Than or Equal to" _
                      & sC & sVal(1)
                  Case Else
                    strDV = sVal(1)
                End Select
              Case Else
                strDV = "'" & sVal(1)
            End Select
           End With
           .Range(.Cells(lRow, 1), .Cells(lRow, 3)).Value _
              = Array(rCell.Address(False, False), sVal(0), strDV)
           Erase sVal
           lRow = lRow + 1
         Next rCell
         .Rows("1:1").Font.Bold = True
         .Columns("A:C").EntireColumn.AutoFit
    End With
  End If

End Sub
 

Download the Sample File

Download the zipped sample Data Validation Documenter file

Excel Tutorials - Data Validation

Data Validation Basics

Create Dependent Lists

Data Validation Criteria Examples

Data Validation Custom Criteria Examples

Data Validation Tips

 

 

More Tutorials

Data Validation Basics

Create Dependent Lists

Data Validation Criteria Examples

Data Validation Custom Criteria Examples

Data Validation Tips

 

 

 

 

 

Privacy Policy

 

Contextures Inc., Copyright 2016
All rights reserved.

 

Last updated: April 6, 2016 10:27 AM