Data Validation

[pincivma]

Hi again
I have many rows with data validation. Sometimes I want to select an item in the list to enter into the cell. When I enter an item that is not in the list I get a message that the item I entered is not in the list. So I then have to go to DATA>DATA VALIDATION>ERROR ALERT and uncheck the box. I have to do this for may rows. My question is this. Is there a global control setting which could look at every cell with data validation in the entire workbook or worksheet and uncheck that box. If not is there a macro that can uncheck that error alert box for every row that has a data validation list.
Thanks

 

[pincivma]

HI Joe
I finally got your macro to work but it takes forever to run due to many worksheets and many DV cells. Not all worksheets have DV cells. So my plan is to only run the macro on only on those sheets that have DV cells. I used your macro and redid it for only one worksheet but the macro did not work. Here is the code. Maybe I'm missing something.

Sub RemoveErrorMessageOnDVCellsMyVersion()
Dim Wsht As Worksheet, DVCells As Range
Application.ScreenUpdating = False
On Error Resume Next
Set DVCells = Wsht.Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
If Not DVCells Is Nothing Then
For Each c In DVCells
With c.Validation
.ShowError = False
End With
Next c
End If
Application.ScreenUpdating = True
End Sub