Hi.
I have an Excel Protected template file (2000') that I need to unprotect
several sheets (20) in order to perform work, then protected back with the
same password to distribute to users.
Currently I am doing this manual which can be time consuming. How can I
create a macro to protect/unprotect sheet with a click of a button?
Thanks for any help and suggestion.

Public const m_cPassword = "MyPassword"

Public Sub ProtectAll()
Dim wks As Worksheet

Application.ScreenUpdating = False
For Each wks In Worksheets
On Error Resume Next
Select Case Trim(wks.Name)
Case "Start" 'Don't protect the start sheet
Case "Main" 'Don't protect the main sheet
Case Else
wks.Protect m_cPassword
End Select
Next wks
Application.ScreenUpdating = True
End Sub

Public Sub UnProtectAll()
Dim wks As Worksheet

Application.ScreenUpdating = False
For Each wks In Worksheets
On Error Resume Next
wks.Unprotect m_cPassword
Next wks
Application.ScreenUpdating = True
End Sub
--
HTH...

Jim Thomlinson


"Cam" wrote:

Hi.
I have an Excel Protected template file (2000') that I need to unprotect
several sheets (20) in order to perform work, then protected back with the
same password to distribute to users.
Currently I am doing this manual which can be time consuming. How can I
create a macro to protect/unprotect sheet with a click of a button?
Thanks for any help and suggestion.

Cam,

This will protect all worksheets in the workbook.
(change protect to unprotect to unprotect all worksheets)
===========================
Dim wksh As Worksheet
Application.ScreenUpdating = False

For Each wksh In ActiveWorkbook.Worksheets
wksh.Protect "password"
Next
Application.ScreenUpdating = True
====================

If you are only protecting some sheets (not all), you will
have to create a loop to identity those sheets.

--
steveB

Remove "AYN" from email to respond
"Cam" wrote in message
...
Hi.
I have an Excel Protected template file (2000') that I need to unprotect
several sheets (20) in order to perform work, then protected back with the
same password to distribute to users.
Currently I am doing this manual which can be time consuming. How can I
create a macro to protect/unprotect sheet with a click of a button?
Thanks for any help and suggestion.

use this macro "before save"


Sub Seal_File()

For Each sheet In Sheets
On Error Resume Next
sheet.Protect ("spw")
Next
Application.StatusBar = ""
End Sub


and this one could be a special key combination
Sub UNSEAL()
ActiveWorkbook.Unprotect ("spw")
For Each sheet In Sheets
On Error Resume Next
sheet.Unprotect ("spw")
Next
Application.StatusBar = "NOT sealed"
End Sub

Note the application status bar commands(optional)
that way you will see at the bottom of your screen whether the sheet is in
the unprotected mode.

Greetings from New Zealand
Bill K
"Cam" wrote in message
...
Hi.
I have an Excel Protected template file (2000') that I need to unprotect
several sheets (20) in order to perform work, then protected back with the
same password to distribute to users.
Currently I am doing this manual which can be time consuming. How can I
create a macro to protect/unprotect sheet with a click of a button?
Thanks for any help and suggestion.