Forum Discussion
Unprotect doesn't work with VBA code
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
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
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 SubHi, HansVogelaar 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
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?