Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Janna
 
Posts: n/a
Default Protect/unprotect all worksheets

Is there a way to easily unprotect all the worksheets in a workbook? For
example, I have a workbook with 20 worksheets (one for each employee). The
worksheets have been individually protected (except for the select cells into
which I enter data. Now with the new year, I would like to unprotect all the
worksheets so I can change the (previously protected) field that contains
the year and then reprotect all the worksheets. Thanks.
  #3   Report Post  
SongBear
 
Posts: n/a
Default

It would depend on if the password is the same in all worksheets, or you have
a list you can reference.
Substitute your password for "password" in the following code

Public pwd As String

Sub UnProtectSheet()
pwd = "password"
Worksheets("sheet1").Unprotect Password:=pwd
End Sub

Sub ProtectSheet()
pwd = "password"
Worksheets("sheet1").Protect Password:=pwd
End Sub

Sub UnptAllSheets()
For Each s In ActiveWorkbook.Worksheets
UnProtectSheet
Next s
End Sub

Sub ptAllSheets()
For Each s In ActiveWorkbook.Worksheets
ProtectSheet
Next s
End Sub

Bear

"Janna" wrote:

Is there a way to easily unprotect all the worksheets in a workbook? For
example, I have a workbook with 20 worksheets (one for each employee). The
worksheets have been individually protected (except for the select cells into
which I enter data. Now with the new year, I would like to unprotect all the
worksheets so I can change the (previously protected) field that contains
the year and then reprotect all the worksheets. Thanks.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
maximum number of worksheets Shooter Excel Worksheet Functions 8 July 1st 06 06:38 AM
How to protect and unprotect 30 worksheets in a file every month . Protect & Unprotect Several Worksheets Excel Worksheet Functions 4 January 10th 05 02:29 PM
HELP! How do you--> Lock a set of rows but also link worksheets to FRUSTRATED Excel Discussion (Misc queries) 6 December 29th 04 11:05 PM
Assigning Cells in worksheets to other data in other worksheets. David McRitchie Excel Discussion (Misc queries) 0 November 27th 04 07:15 PM
Sort/Link Worksheets Debby Excel Worksheet Functions 0 November 12th 04 08:50 PM


All times are GMT +1. The time now is 11:24 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"