Forum Discussion
Unprotect doesn't work with VBA code
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
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
8 Replies
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.
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 Sub
