Search Contextures Sites

Excel -- Data Validation -- Documentation

Document a Worksheet's Data Validation: Text File
Document a Worksheet's Data Validation: Worksheet

Download the zipped sample Data Validation Documenter file


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

 

  

 

 

Excel Tutorials - Data Validation

Data Validation Basics
Data Validation - Create Dependent Lists
Data Validation - Dependent Dropdowns from a Sorted List  
Data Validation - Dependent Lists With INDEX  
Hide Previously Used Items in a Dropdown List
Data Validation - Display Messages to the User
Data Validation - Display Input Messages in a Text Box 
Data Validation - Use a List from Another Workbook
Data Validation Criteria Examples
Data Validation Custom Criteria Examples
Data Validation Tips
Data Validation Documentation
Data Validation Combo Box     
Data Validation Combo Box - Named Ranges
Data Validation Combo Box -- Click
Data Validation - Add New Items  

 

Privacy Policy

 

Contextures Inc., Copyright ©2013
All rights reserved.

 

Last updated: May 30, 2013 3:35 PM