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?

  • 1
    This is a great question. The Validation object is not like the Names object - Validation is tied to a specific Range. Hmmm... – DaveParillo Jan 9 '10 at 1:27

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


  • 1
    This is Excel 2007, but I would think the 2003 model will be similar to this. – Glenn M Jan 12 '10 at 2:34
  • this is the basic approach I used. the range A1:AZ4000 was tested with For Each cell in Range, collecting the Validation.ErrorTitle, and Validation.ErrorMessage with their cell.addresss and it was written to another sheet to see the results. Then I could write conditional rules for changing the Messages and Titles. Lots of fun inheriting someone elses prior work. Thanks Glenn, not exactly what I needed, but helpful +1. If anyone knows where this all is stored in the workbook, I am still very curious. – datatoo Jan 12 '10 at 14:22
  • Are you asking where it's stored in the BIFF8 binary file? – dkusleika Jan 15 '10 at 19:49
  • I am not sure how or where it is stored, as there are a number of cell ranges in any given sheet. Mostly I would like a simple way to edit the contents that are stored. You can get at the information, but indirectly. – datatoo Jan 16 '10 at 17:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.