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

 

[jasonb75]

When you edit the first one, check the box at the bottom that says 'Apply these changes to all other cells with the same settings'

 

[JoeMo]

Here's an untested macro you can try on a copy of your workbook. You can put this in your Personal.xlsb workbook then put an icon for it on the QAT so it can be run on any active workbook with a click of the icon.

Sub RemoveErrorMessageOnDVCells()
'Put this in your Personal.xlsb workbook and run it from the QAT with target workbook active
Dim tgtWB As Workbook, Wsht As Worksheet, DVCells As Range
Application.ScreenUpdating = False
Set tgtWB = ActiveWorkbook
With tgtWB
    For Each Wsht In .Worksheets
        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
        Else
            MsgBox "No DV cells found on worksheet: " & Wsht.Name
        End If
    Next Wsht
End With
Application.ScreenUpdating = True
End Sub

 

[pincivma]

Hi Jason WOW That was easy. Thank you so much.
Hi Joe. I tested your macro and I got an error on the line
.ShowError = False

I did not put the code on my Personal.xlsb workbook. Does that make a difference?

 

[JoeMo]

What was the error message you received? Does your workbook have cells with DV not based on a List?

 

[pincivma]

The error I received did not make sense to me. The error occurred on the line
.ShowError = False
The error message I got is as follows:
Run time error 1004
Application-defined or object-defined error
My workbook has cells with DV that are based on many Lists. But the lists are on a separate worksheet. For example one list is on say sheet 3 with the source =Drape

 

[JoeMo]

Sorry, I can't reproduce that error, even with DV cells on a different sheet than their List is on. Did you copy the code directly from your browser and paste it into a standard module in your workbook?

 

[pincivma]

Yes I did. I think I know why I got the error. All of my drop down DV lists on sheet1 from cell B9 to cell B400 do not reference the same range in other worksheets that have the lists. For example Cells B9 and B10 references the list in sheet 10 range A1:A20 with the reference name =Drape
Then Cells B12 and B13 references the list in sheet 10 range B1:B20 with the reference name =Tech
This goes on up to row 400 with 100 different reference names in the DV box in the settings tab. I don't think that a macro will work on just sheet1 let alone on many worksheets.

 

[JoeMo]

The macro should be identifying all cells with DV on a worksheet, regardless of what list they are attached to. Once a cell is identified, the macro unchecks the show error message box. So, I don't think your use of multiple List references is the cause of the error, but I haven't tested the macro on more than a single range with a DV list on another sheet.

 

[pincivma]

OK. Give that a try and see if you get the error. If not, then I don't know why I'm getting the error.