Data validation - error alerts

[ledgreg]

Hi,

Is there a way to turn off the data validation error alert for an
entire workbook without resetting all the data validations I have
setup? Or is there a value I can add to one of the cells in my data
validation source list to allow users to type in values not included in
the list?

I have hundreds of cells using data validation and really don't want to
turn off the error alert for each one individually. At one time I had
the ability to enter in anything I wanted in a data validated cell
without turning off the error alert. I assumed it was because one of
the cells in my list had nulls or spaces in it. But I can no longer
seem to get that to work....

I sure would appreciate any thoughts that may save me the tedious
process of having to turn off error alerts for hundreds of cells...

Thanks,
Greg

 

[Jim Rech]

To turn off all validations on the active sheet run this:

Sub ValidationCtrl()
Dim Cell As Range
For Each Cell In Cells.SpecialCells(xlCellTypeAllValidation)
Cell.Validation.ShowError = False
Next
End Sub

Change to True and re-run to turn validations back on.

 

[ledgreg]

Thanks so much Jim you just saved me a ton of time!!!

Take care,
Greg