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

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.

--
Jim
"ledgreg" wrote in message
oups.com...
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

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

Take care,
Greg