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

 

[PeteWright]

The problem is that VBA Code cannot be executed on protected Workbooks or Worksheets.

Try to add the following line at the beginning of your code:

Me.Protect UserInterfaceOnly := True

 

[VIorica000]

The problem is that VBA Code cannot be executed on protected Workbooks or Worksheets.

Try to add the following line at the beginning of your code:

Me.Protect UserInterfaceOnly := True

Hi, thanks for the answer,

but it doesn’t work, it allows the paste copy without giving me the alert

Thanks