I have a workbook with 65 sheets in it. I would like to be able to protect all the sheets with one entry/password instead of going through and protecting each page. Can anyone help?
I have a workbook with 65 sheets in it. I would like to be able to protect all the sheets with one entry/password instead of going through and protecting each page. Can anyone help?
Scooter172
Try this
Go to Insert>Module and in here add the code belowPrivate Sub CommandButton1_Click() Dim wSheet As Worksheet For Each wSheet In Worksheets If wSheet.ProtectContents = True Then wSheet.Unprotect Password:=TextBox1.Text Else wSheet.Protect Password:=TextBox1.Text End If Next wSheet Unload me End Sub
Please let me know if you're still unable to workout the code.Sub ShowPass() UserForm1.Show End Sub
Best Regards,
adamsm
[vba]Sub ProtectAll()
For c = 1 To 58
Sheets(c).Select
ActiveSheet.Unprotect
Next c
Sheets(1).Select
End Sub
Sub UnprotectAll()
For c = 1 To 58
Sheets(c).Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingColumns:=True, AllowFormattingRows:=True
Next c
Sheets(1).Select
End Sub
[/vba]Originally Posted by adamsm
Scooter172
Doesn't my version help you to get what you want?
Best Regards,
adamsm
It protects the page your on and as soon as you click in a cell it unlocks the page.
I installed the Command button as well to activate the code.
Scooter172
Does this make you happy? It works fine with me.
Best Regards,
adamsm
Maybe this?
Last edited by FF Ethan; 05-21-2010 at 09:50 AM.
Ethan Ellis
Before VBA Express After VBA Express.
Sorry hold on that last post was a mistake.
Ethan Ellis
Before VBA Express After VBA Express.
[VBA]Sub protectAll()
Dim myCount 'Optional
Dim i 'Optional
myCount = Application.Sheets.Count
Sheets(1).Select 'Optional
For i = 1 To myCount
ActiveSheet.Protect
If i = myCount Then
End
End If
ActiveSheet.Next.Select
Next i
End Sub
[/VBA]
Ethan Ellis
Before VBA Express After VBA Express.
I use this:
Sub ProtectUnprotect() Dim wSheet As Worksheet, strMsg As String For Each wSheet In Worksheets If wSheet.ProtectContents = True Then wSheet.Unprotect Else wSheet.Protect End If Next wSheet End Sub
Thank you for your help
A couple of things to note here, Ethan, your variables are all variant unless specified with each variable, and there is no need here to select anything as you are. You can loop through all sheets, either by object or index number (as you have) and not select the object. I would also recommend explicitly referencing the worksheet to the workbook, as it will lessen confusion/troubleshooting/maintenance with multiple workbooks being open, as your code will be run on the activeworkbook. Also, the If/Then statement in the For/Next loop isn't doing anything, as it would end in the next line anyway, you're just forcing all code to stop. Just a couple things to think about.Originally Posted by FF Ethan
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
Zack, Thanks for the code, this works great but Idealy I need to have both a protect and unprotect, preferably with a password. Would it be the same routine only using Unprotect in the language?
Scott
Originally Posted by Zack Barresse
Last edited by Scooter172; 05-25-2010 at 11:45 PM.
Scooter172
The code wasn't mine, it was Ethan's. I was just quoting his post and giving a few suggestions. Read ndendrinos' post. It's basically the same thing - loop, check locking state, protect or unprotect. The only thing not posted in that code is the password. If you don't know how that syntax works, there are examples in this thread, or I recommend the help files (from the VBE, select the word, press F1, great source for checking syntax).
HTH
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables