Worksheet protection

J

Jeannine

I want to lock all of the cells in all worksheets that have formula's. I
have 26 pages and the formula's are in the same cells on each page.

I want to protect the formula's but the only way I can find to do this is by
protecting one page at a time. So everytime I need to change a formula, I
have to unprotect each page, change the formula, then protect each page
again. Is there a way to protect all of the pages at one time? When I try to
use the protection for the workbook, it doesn't seem to do anything.
 
M

Maistrye

Jeannine said:
I want to lock all of the cells in all worksheets that have formula's.
I
have 26 pages and the formula's are in the same cells on each page.

I want to protect the formula's but the only way I can find to do thi
is by
protecting one page at a time. So everytime I need to change
formula, I
have to unprotect each page, change the formula, then protect eac
page
again. Is there a way to protect all of the pages at one time? When
try to
use the protection for the workbook, it doesn't seem to do anything.
Click to expand...

Try this: (you can prompt for a password too, if you want)

Sub ToggleProtection()
Dim aWorksheet As Worksheet
Dim res As Integer

res = MsgBox("Yes = Protect All Sheets, No = Unprotect All Sheets"
vbYesNo)

If (res = vbYes) Then
For Each aWorksheet In Worksheets
aWorksheet.Protect "MyPassword"
Next aWorksheet
Else
For Each aWorksheet In Worksheets
aWorksheet.Unprotect "MyPassword"
Next aWorksheet
End If

End Sub

Scot
 
J

Jeremy

I tried this but I get a VB error on the fourth line (the res=MsgBox etc
line.

Any ideas?

J.
 
D

Dave Peterson

This is all one logical line:

res = MsgBox("Yes = Protect All Sheets, No = Unprotect All Sheets", vbYesNo)
 
J

Jeannine

Thank you so much Maistrye, It works great. To run it I go to macro, run.
Is there a way to put a password on so that I can prevent other people from
running the macro?
 
D

Dave Peterson

Maybe...

Option Explicit
Sub ToggleProtection()
Dim aWorksheet As Worksheet
Dim Resp As String
Dim res As Integer

Resp = InputBox(Prompt:="Please enter a password to run")

If Resp <> "hithere" Then
MsgBox "nope!!!"
Exit Sub
End If

res = MsgBox("Yes = Protect All Sheets, No = Unprotect All Sheets", vbYesNo)

If res = vbYes Then
For Each aWorksheet In Worksheets
aWorksheet.Protect "MyPassword"
Next aWorksheet
Else
For Each aWorksheet In Worksheets
aWorksheet.Unprotect "MyPassword"
Next aWorksheet
End If

End Sub
 
Top