Protect/Unprotect in excel

[marsderj]

I am fairly new to the VBA world, so any assistance would be greatly appreciated!
I have a worksheet which is intended for user input and contains a specific column with data validation drop down lists. I have code that prevents the user from pasting into this specific column and forces them to use the drop down selection. I also want to protect the worksheet with a password "secret". When I protect the sheet, the code that prevents the pasting into the data validation no longer works. I have tried the code below and the worksheet remains protected however it still allows me to paste over the data validation. It is as if the two are mutually exclusive. Any ideas?

Sub unprotectworksheet()
Sheets("P24").Unprotect Password:="secret"
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
'Does the validation range still have validation?

If HasValidation(Range("ValidationRange")) Then
Exit Sub
Else
Application.Undo
MsgBox "You can not paste in this cell. " & _
"It would have deleted data validation rules. ", vbCritical
End If

End Sub
Private Function HasValidation(r) 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
Sub protectworksheet()
Sheets("P24").Protect Password:="secret"
End Sub

 

[yakpol]

Try
Sheets("P24").protect password:="secret", userinterfaceonly:=true

 

[marsderj]

Still no luck. I am not sure that its just not my version of excel. It is as if it is not even protecting the sheet when I use the protect method. Have you used the code successfully?

 

[yakpol]

It did work. Here's the code:

Sub test()
Dim wks As Worksheet
Set wks = ActiveSheet
wks.Protect "secret", , , , True
Range("A4") = 2
End Sub

 

[marsderj]

Thanks. I did get that code to work on a new worksheet. I guess the issue that a user would still be able to paste over a data validation cell even with the sheet protected. My hope was the I could give a user the worksheet and they would not be able to paste over my data validation cells, rather they would be forced to pick from the list. I want the user to pick from the list and I also want to protect and hide the formulas in the Column C,E,etc. The user also needs to be able to freely enter data into columns F-Q, so a full sheet protection would not work. Any ideas? Thanks again for your help with this!

 

[yakpol]

You are right, if cell is protected you cannot use datavalidation list to change it, if it's unprotected you can paste into it. You can try to disable copy/paste if you so concerned. In worksheet module include event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
   
End Sub

It shall contain a code which will reset current selection making copy impossible. Look for help on worksheet object events.