PDA

View Full Version : Protection



Scooter172
05-20-2010, 09:08 PM
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?

adamsm
05-21-2010, 04:13 AM
Try this

Private 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
Go to Insert>Module and in here add the code below

Sub ShowPass()

UserForm1.Show

End Sub
Please let me know if you're still unable to workout the code.:hi:

Scooter172
05-21-2010, 04:29 AM
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

Try this

Private 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[/code]
Go to Insert>Module and in here add the code below
[code]Sub ShowPass()

UserForm1.Show

End Sub
Please let me know if you're still unable to workout the code.:hi:

adamsm
05-21-2010, 05:25 AM
Doesn't my version help you to get what you want?

Scooter172
05-21-2010, 05:31 AM
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.

adamsm
05-21-2010, 05:51 AM
Does this make you happy? It works fine with me.

FF Ethan
05-21-2010, 08:58 AM
Maybe this?

FF Ethan
05-21-2010, 09:00 AM
Sorry hold on that last post was a mistake.

FF Ethan
05-21-2010, 09:02 AM
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

ndendrinos
05-22-2010, 04:30 AM
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

Zack Barresse
05-24-2010, 03:23 PM
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

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. :cool:

Scooter172
05-25-2010, 11:31 PM
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: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. :cool:

Zack Barresse
05-28-2010, 08:25 AM
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