Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Protect/Unprotect in excel

Protect/Unprotect in excel

Protect/Unprotect in excel

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
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  

RE: Protect/Unprotect in excel

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


RE: Protect/Unprotect in excel

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?   

RE: Protect/Unprotect in excel

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

RE: Protect/Unprotect in excel

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!

RE: Protect/Unprotect in excel

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.  

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close