Excel -- Data Validation -- Documentation


Document a Worksheet's Data Validation

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.

Download the zipped sample file

 
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

 

  

 

 
1. Data Validation
2. Data Validation -- Create Dependent Lists
3. Hide Previously Used Items in a Dropdown List
4. Display Messages to the User
5. Use a List from Another Workbook
6. Validation Criteria Examples
7. Custom Validation Criteria Examples
8. Data Validation Tips
9. Data Validation Documentation   
10 Data Validation -- Combo box     
11. Data Validation -- Combo Box - Named Ranges
12. Data Validation -- Display Input Messages in a Text Box 
13. Data Validation -- Dependent Dropdowns from a Sorted List  
   
       Home     Excel Tips     Excel Files      Blog    Contact

 

RSS Feed

 

 

 

The Excel Store

Last updated: July 5, 2008 10:54 PM