Macro to protect/unprotect worksheets
 

Hello,

I have 40 worksheets (named Data 1,.. 40)and a "Summary"
sheet in an Excel file and They are sheet protected with
the same password.
I would like to achieve two things:
1) Create a macro to protect and unprotect the all the
sheets at once with a click of a button or macro.
2) Sort by Column B only in sheet "Summary" from data
range A3:N42 in ascending order.

The reason for the first thing is so If I need to modify
some cells in all the Data sheets, I don't have to
unprotect all 40 sheets once at a time.
NOTE: all the data sheets have the same format and and
structure.

Any help would be appreciated. Thanks

Macro to protect/unprotect worksheets
 

One of JE McGimpseys with a slight tweak.

Public Sub ToggleProtect()

Const PWORD As String = "ken"
Dim wkSht As Worksheet
Dim statStr As String
Ans = InputBox("If Unprotecting Sheets, then enter correct Password Please" & vbCrLf &
vbCrLf & _
"Else hit enter - Sheets will be saved with Default Password ")

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



"Cameron" wrote in message
...
Hello,

I have 40 worksheets (named Data 1,.. 40)and a "Summary"
sheet in an Excel file and They are sheet protected with
the same password.
I would like to achieve two things:
1) Create a macro to protect and unprotect the all the
sheets at once with a click of a button or macro.
2) Sort by Column B only in sheet "Summary" from data
range A3:N42 in ascending order.

The reason for the first thing is so If I need to modify
some cells in all the Data sheets, I don't have to
unprotect all 40 sheets once at a time.
NOTE: all the data sheets have the same format and and
structure.

Any help would be appreciated. Thanks

Macro to protect/unprotect worksheets
 

"Cameron" wrote in message
...
Hello,

I have 40 worksheets (named Data 1,.. 40)and a "Summary"
sheet in an Excel file and They are sheet protected with
the same password.
I would like to achieve two things:
1) Create a macro to protect and unprotect the all the
sheets at once with a click of a button or macro.

Sub Unprotect_All()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect password:="your_password"
Next ws
End Sub

this will loop through every worksheet in the book and unprotect it
(assuming the passwords are all your_password)

2) Sort by Column B only in sheet "Summary" from data
range A3:N42 in ascending order.


Sub sort_summary()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Summary")
ws.Range("A3:N42").Sort key1:=ws.Range("B:B")
Set ws = Nothing
End Sub

The sort should default to ascending order.

have fun.

Mike
--

__________________________________________________ __________________________
________________
Please reply to newsgroup so everyone can benefit.
Email address is not valid (see sparkingwire.com)
__________________________________________________ __________________________
________________


The reason for the first thing is so If I need to modify
some cells in all the Data sheets, I don't have to
unprotect all 40 sheets once at a time.
NOTE: all the data sheets have the same format and and
structure.

Any help would be appreciated. Thanks