Contextures

Excel Data Validation Documentation

Use this macro to create Excel data validation documentation. Get a list of list of all data validation on Excel worksheet.

NOTE: If you have a copy of my Contextures Excel Tools add-in, it has a button to create a list of all the data validation on the active sheet.

List All 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 

go to top

List All 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
 

go to top

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

Search Contextures Sites

 

Excel Tools Add-in

 

Excel Data Entry Popup List

 

 

Excel Data Entry Popup List

 

 

Excel Data Entry Popup List

 

Last updated: December 23, 2016 11:36 AM
Contextures RSS Feed