Unprotect doesn't work with VBA code

[Viorica000]

Hello everyone,

I have the following code which that does not allow the user to paste values in a Data Validation List Column. 

My problem is that I have columns (without data validation) that have to stay protected,

columns With Data Validation List have no locked option from format cell, but when i enable the protect sheet my VBA code doesn't work, i try everything with worsheet.unprotect password but doesn't work. 

Also if I try to copy a cell from a column with Data Validation and paste on another column that has Data Validation the VBA doesn't work. 

With unprotected sheet and value which does not come from Data Validation the VBA works!. 

I don’t know where I’m wrong, I really need help 

thank you very much

Private Sub Worksheet_Change(ByVal Target As Range)

Dim xValue As String
Dim xCheck1 As String
Dim xCheck2 As String

If Target.Count > 1 Then

Exit Sub
End If
Application.EnableEvents = False
xValue = Target.Value
On Error Resume Next
xCheck1 = Target.Validation.InCellDropdown
On Error GoTo 0
Application.Undo
On Error Resume Next
xCheck2 = Target.Validation.InCellDropdown
On Error GoTo 0
If xCheck1 = xCheck2 Then
Target = xValue
Else
MsgBox "It is not allowed to paste!"
End If
Application.EnableEvents = True

End Sub

[#2]

@Viorica000  I didn't have any trouble using the code you inserted in your post.  I don't fully understand what the problem is, perhaps you have more code to share?  You mentioned that your main issue is with protecting and unprotecting, so any relevant protection code would help us to identify the issue.

[#3]

Hi @TragicReindeer  thanks for the answer
Yes, ma case is:
Have 3 column, where column A and B have "Format cells.."-->"Protection" -->"Locked"
Column C doesn't have Locked option because is my Data Validation List. 

When i Protect my sheet, the code VBA doesn't work anymore, if i remove the protection the vba works. 

So if the sheet is protected and I paste, I don’t get the error message.

thank you very much

[#4]

@Viorica000 

You might unprotect/reprotect the sheet in the code. Replace "secret" with the password you used ("" if none).

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xValue As String
    Dim xCheck1 As String
    Dim xCheck2 As String

    If Target.Count > 1 Then
        Exit Sub
    End If

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Me.Unprotect Password:="secret"

    xValue = Target.Value
    On Error Resume Next
    xCheck1 = Target.Validation.InCellDropdown
    On Error GoTo 0
    Application.Undo
    On Error Resume Next
    xCheck2 = Target.Validation.InCellDropdown
    On Error GoTo 0
    If xCheck1 = xCheck2 Then
        Target = xValue
    Else
        MsgBox "It is not allowed to paste!"
    End If

    Me.Protect Password:="secret"
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

[#5]

Hi, @Hans Vogelaar thanks for the answer

Unfortunately it does not work, the sheet is protected and when try to copy paste the value is pasted without giving me the alert.

So I think it’s the Unprotect function that doesn’t work, because when I manually remove the protection of sheet and I try to copy paste, I get out the alert and the sheet protected automatically.

thank you very much

Have a good day 

[#6]

@Viorica000 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#7]

@Hans Vogelaar 

Yes of corse,   https://file.io/eNw3LY59m7te

thanks 

[#8 · Accepted solution]

@Viorica000 

Thanks, that is very useful. I see what happens:

When the sheet is protected, Excel only copies/pastes the value, not the cell's formatting (including the data validation rule). So xCheck2 will always be the same as xCheck1.

On the plus side, the data validation of the target cell will not be overwritten.

But the downside is that you can paste a value that is not part of the data validation list (if any).

I don't see a solution, except altogether forbidding to paste to a cell with a data validation drop down:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xCheck1 As Boolean
    If Target.Count > 1 Then
        Exit Sub
    End If
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    On Error Resume Next
    xCheck1 = Target.Validation.InCellDropdown
    On Error GoTo 0
    If xCheck1 Then
        MsgBox "It is not allowed to paste!"
        Application.Undo
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

[#9]

Thank you very much!