Re: Generic protect/unprotect code through buttons and code?


StargateFanFromWork wrote:
> I looked through the archives but have seen that there can be problems with
> protecting/unprotecting sheets with vb so thought I would just ask about
> this one. I have buttons on some of my sheets. I'm ready to protect the
> sheets whereas up till now they remained unprotected. Apparently, we can
> unprotect ***, run code, then protect *** again. (I'm assuming buttons
> will still be clickable even though protected??)
>
> Also, we'll need a generic line of coding for protecting/unprotecting -
> i.e., without any *** names and/or passwords - as that would be easiest to
> deal with as then I could simply copy those lines to all the current
> workbooks. It would mean adding a simple unprotect line after title of
> script and have code then execute rest of script - such as sort, etc. - and
> then protect the *** before script finishes.
>
> Also, people have posted about problems with passwords so we won't need any
> passwords. And the other problem I saw is that code might not work if ***
> protected manually, etc. So it was recommended in that particular post to
> use an IF statement. I haven't been able to find an example of code that
> will do these 2 things so begging indulgence of group to help with this one.
>
> This is final step in completing all the workbooks I made here at this job 1
> 1/2 yrs ago. They've been unprotected all this time. Hopefully I can close
> them up yet still retain full functionality with all the buttons/code! <g>
>
> Thank you!! :oD


I've been using this for some time with good success. I have the main
macro assigned to a custom button on my main toolbar, and it works as a
toggle to Protect or Unprotect, as I require. The "unprotect" routine
works regardless of the Protection password used (if any).

Sub ProtectionToggle()
Application.ScreenUpdating = False

If Active***.ProtectContents = True Then
' Unprotect -- a valid command by itself for non-password
protected sheets
ThisSheetPassword
Else
Protect
End If

Application.ScreenUpdating = True
End Sub

******************************************

Sub ThisSheetPassword()
' Obtained from pcc at MrExcel.com in response to my query
' about updated PASSWORDS.XLA (to unprotect sheets protected by
' an unknown password).

On Error Resume Next
Active***.Protect "", , , , True
Active***.Range("a1").Copy Active***.Range("a1")

End Sub

******************************************

And if I have a workbook full of protected sheets, and want to
unprotect all of them for mass edits or whatever reason, then I use
this one:

Sub SheetPassword()
' Obtained from pcc at MrExcel.com in response to my query
' about updated PASSWORDS.XLA (to unprotect sheets protected by
' an unknown password).

Start*** = Active***.Name
On Error Resume Next
For myCounter = 1 To Worksheets.Count
Worksheets(myCounter).Select
Active***.Protect "", , , , True
Active***.Range("a1").Copy Active***.Range("a1")
Next myCounter

Sheets(StartSheet).Select
End Sub

.