Where is data validation text stored in Excel?

Question

I have an Excel 2003 sheet with lots of different data validation rules. Is there anyway that they can be viewed other than through the regular data validation dialog?

It would be helpful to export a list with validation error alerts, and rules, and check that list rather than through the dialog.

Anyone know if this is possible? Or how to build a macro to do this?

If this question should be migrated to StackOverflow, how would I do that?

Answer 1

There is an Excel VBA Validation object, associated with the range. See code:

With Range("e1").Validation
.Add Type:=xlValidateWholeNumber, _
    AlertStyle:=xlValidAlertInformation, _
    Minimum:="5", Maximum:="10"
.InputTitle = "Needs Wholenumber"
.ErrorTitle = "Integers"
.InputMessage = "Enter an integer from five to ten"
.ErrorMessage = "You must enter a number from five to ten"
End With

These properties are readable so you could pull out the the .InputTitle or .InputMessage or the min and max values allowed for that cell's validation programatically, to see what validadtion is being used.

Try this:

Sub test()
Range("a1") = Range("e1").Validation.InputTitle & ": Range = " & Range("e1").Validation.Formula1 & " to " & Range("e1").Validation.Formula2
End Sub

The above code returns to cell A1: Needs Wholenumber: Range = 5 to 10. See books online for more info. http://msdn.microsoft.com/en-us/library/aa224495(office.11).aspx

Glenn