Unprotecting several excelsheets at once 4

[flaviooooo]

Hey,

I have a protected Excel workbook. But when i need to update the sheets, i have to unprotect them one at a time...
Isn't there a way (macro?) so that i only have to type in the password once, and then it unprotects all the other sheets as well?

Thanks

 

[Sypher2]

I use the following macro to toggle protection on all the sheets in a workbook. You can add your password after the Unprotect/Protect methods.

[tt]
Sub Toggle_Protection()
Dim mySheet As Worksheet
Dim myCell As Range
Application.ScreenUpdating = False 'Eliminate flicker
Set myCell = ActiveCell 'Remember cell pointer location
For Each mySheet In Worksheets 'Iterate thru all worksheets
Select Case mySheet.ProtectContents 'Is worksheet protected?
Case True 'Worksheet is currently protected
mySheet.Unprotect
Case False 'Worksheet is currently unprotected
mySheet.Protect
End Select
Next mySheet
myCell.Activate 'Go back to original location
Application.ScreenUpdating = True 'Reinstate screen updating
End Sub
[/tt]

 

[KenWright]

A couple of macros I have for that, including password


Public Sub ToggleProtect1()
Const PWORD As String = "ken"
Dim wkSht As Worksheet
Dim statStr As String

For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
statStr = statStr & vbNewLine & "Sheet " & .Name
If .ProtectContents Then
wkSht.Unprotect Password:=PWORD
statStr = statStr & ": Unprotected"
Else
wkSht.Protect Password:=PWORD
statStr = statStr & ": Protected"
End If
End With
Next wkSht
MsgBox Mid(statStr, 2)
End Sub


Sub Toggleprotect2()
Const PWORD As String = "ken"
Dim wkSht As Worksheet

For Each sh In ActiveWorkbook.Worksheets
If sh.ProtectContents = False Then
sh.Protect PWORD
Else
sh.Unprotect PWORD
End If
Next sh
End Sub

Regards
Ken............

 

[flaviooooo]

Thanks for the help guys, but I keep getting the following errormessage:

runtime error 1004

Method 'Unprotect' of Object '_Worksheet' failed


Any thoughts?

 

[flaviooooo]

Hey,

when I just put the code in a macro, and run that macro without the use of a button or so, it works fine.

But when I use a button, it gives the error.
And I need to use a button, because I need to have a masked passwordfield.

aargh

 

[xlbo]

Heh heh - you be using the Controls toolbox button. You need to set the TakeFocusOnClick property of the button to FALSE
This happens soooooooooooooo often Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[flaviooooo]

hehe cool, ok

thanks for the help mate

Have a star on me

 

[mudstuffin]

Thanks for that Geoff.

I have had the same predicament and it has (no longer though) been buggin the hell out of me.

Here's a star from me too.....

Regards,


mudstuffin.

 

[KenWright]

The other fix is to use the button from the 'Forms' toolbar of course.

Regards
Ken..............