How can I document the Data Validation used in a worksheet?
I'm trying to document a worksheet. Is there a way to save all the data
validation parameters to a file other than cutting & pasting each entry? |
This might be a start. It saves the type and formula parameters of the
validation object in the cells in the active sheet which contain validation. It doesn't save input strings or error strings, though those could be added: Public Sub DocumentValidation() Dim sVal As Variant Dim rValidation As Range Dim rCell As Range Dim nFile As Long Dim sC As String sC = Chr(9) On Error Resume Next Set rValidation = Cells.SpecialCells(xlCellTypeAllValidation) If Not rValidation Is Nothing Then nFile = FreeFile Open "test.txt" For Output As #nFile For Each rCell In rValidation ReDim sVal(0 To 3) With rCell.Validation sVal(0) = Choose(.Type + 1, "Input Only", _ "Whole Number", "Decimal", "List", "Date", _ "Time", "Text Length", "Custom") sVal(1) = .Formula1 sVal(2) = Choose(.Operator, "And", "Or", "Top 10", _ "Bottom 10", "Top 10%", "Bottom 10%") sVal(3) = .Formula2 End With Print #nFile, rCell.Address(False, False) & sC & _ sVal(0) & sC & sVal(1) & sC & sVal(2) & _ sC & sVal(3) sVal = Empty Next rCell Close #nFile On Error GoTo 0 End If End Sub If you're new to macros, check out David McRitchie's "Getting Started with Macros" web page: http://www.mvps.org/dmcritchie/excel/getstarted.htm In article , "New VA User" <New VA wrote: I'm trying to document a worksheet. Is there a way to save all the data validation parameters to a file other than cutting & pasting each entry? |
All times are GMT +1. The time now is 08:40 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com