Data Validation Macro stops working when worksheet is protected

[keeferino]

Hey guys, I'm using a macro to ensure that data validation hasn't been pasted over and everything is working fine, until I protect the workbook. Only the first row of data is protected and user are allowed to make almost any modification aside from inserting new columns. The hang up is the HasValidation function. Once the workbook is protected the function always returns 0 errors even if i have pasted in an invalid value. I've been searching all morning and can't come up with an answer.

I've tried including code to unprotect the worksheet but the function still doesn't pick up the invalid value.

Private Sub Worksheet_Change(ByVal Target As Range)
[COLOR=#0000ff]    'Does the validation range still have validation?[/COLOR]
    If HasValidation(target.column) Then
        Exit Sub
    Else
        Application.Undo
        MsgBox "Your last operation was canceled." & _
        "It would have deleted data validation rules.", vbCritical
    End If
End Sub

Private Function HasValidation(r) As Boolean
[COLOR=#0000ff]'   Returns True if every cell in Range r uses Data Validation[/COLOR]
    On Error Resume Next
    x = r.Validation.Type
    If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function

 

[JoeMo]

When I step through everything is fine until the last line of the function and [B]no matter what I paste in the cell it returns true[/B]. When I protect the sheet I have the header row cells locked but nothing else.[/QUOTE]

That suggests that your data validation is intact after the paste. Not all data validation is destroyed by pasting. Have you looked after a paste to see if the data validation is intact?

 

[keeferino]

The paste clears the validation.

 

[JoeMo]

The paste clears the validation.

I added two lines to your code (w/o which I don't see how you could run it and not have it run endlessly) and it works fine for me when I protect the sheet and try to paste to unlocked cells.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim columnNumber
'Dim columnHeader
'columnHeader = Cells(1, Target.Column)
columnNumber = Target.Column

Select Case columnNumber
Case 2
'Type of Change
'Does not allow data validation override by copy/paste
        If HasValidation(Columns(columnNumber)) Then
            Exit Sub
        Else
            Application.EnableEvents = False
            Application.Undo
            MsgBox "You must choose a valid response from the DropDown.", vbCritical
            Application.EnableEvents = True
        End If

End Select
End Sub

'Function for Data Validation sub
Private Function HasValidation(r As Range) As Boolean
'   Returns True if every cell in Range r uses Data Validation
    On Error Resume Next
    x = r.Validation.Type
    If Err.Number = 0 Then HasValidation = True Else: HasValidation = False
End Function

 

[keeferino]

Not sure what you're doing to get it to work. I created a test spreadsheet and when I paste a value thats not on the data validation list in there the function still returns true.

 

[JoeMo]

Not sure what you're doing to get it to work. I created a test spreadsheet and when I paste a value thats not on the data validation list in there the function still returns true.

Did you copy the code I posted in post #13?

 

[keeferino]

Yup.

 

[keeferino]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim columnNumber
'Dim columnHeader
'columnHeader = Cells(1, Target.Column)
columnNumber = Target.Column

Select Case columnNumber
Case 2
'   Returns True if every cell in Range r uses Data Validation
    On Error Resume Next
    x = Columns(columnNumber).Validation.Type
    If Err.Number = 0 Then
        Exit Sub
    Else
        Application.EnableEvents = False
        Application.Undo
        MsgBox "You must choose a valid response from the DropDown.", vbCritical
        Application.EnableEvents = True
    End If

End Select
End Sub

I tried moving the function into the sub like this and I ran into the same issue. It works just fine until I protect the worksheet, then it always returns true.

 

[keeferino]

Cross posted this here. Data Validation Macro stops working when worksheet is protected

I uploaded a test workbook there but cant figure out how to get it uploaded here. Will try again after lunch.

 

[keeferino]

That suggests that your data validation is intact after the paste. Not all data validation is destroyed by pasting. Have you looked after a paste to see if the data validation is intact?

Ok, so it looks like this is the issue. Sorry I didnt notice before. When the workbook is protected, the data validation list stays intact, but the wrong value is still being pasted into the cell.

 

[JoeMo]

Ok, so it looks like this is the issue. Sorry I didnt notice before. When the workbook is protected, the data validation list stays intact, but the wrong value is still being pasted into the cell.

As I said in Post #6. Not all data validation is destroyed by pasting, even when an invalid value is pasted. Review the alternate solution I offered you way back on post #6. That solution will not prevent pasting to cells that are unlocked but have no validation, but it will not allow pasting to unlocked validated cells.