Macro to protect/unprotect with password

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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.