Where is data validation text stored in Excel?

Where is data validation text stored in Excel? "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?"
Asked by: Guest \| Views: 403

Total answers/comments: 1

Comments display order:

Guest [Entry]

"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"