protect all sheets macro

dislecxe · Mar 21st, 2002, 02:49 PM

Can you write a macro to protect all sheets? I have many workbooks that have many sheets. They are veiwed by many people. I have to revise them frequently. The normal Tools, Protection, etc. on every sheet is a pain.

Mudface · Mar 21st, 2002, 02:57 PM

Following should work OK, change the password to suit :-

Public Sub ProtectAll()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.Protect password:="MyPass", DrawingObjects:=True, Contents:=True, Scenarios:=True
Next ws

End Sub

dislecxe · Mar 21st, 2002, 03:16 PM

Thanks, It works fine.

shades · Mar 21st, 2002, 03:32 PM

I have many password protected workbooks/sheets. This is a great help. Thanks.

Now, can the reverse be done? Unprotecting all sheets at one time?

Mudface · Mar 21st, 2002, 03:37 PM

(Sort of) the reverse: -

Public Sub DeProtectAll()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect password:="MyPass"
Next ws

End Sub

shades · Mar 22nd, 2002, 09:49 AM

Thanks. This will save me many, many keystrokes!!

Mar 22nd, 2002, 09:49 AM	#6
shades Board Regular Join Date: Mar 2002 Location: Near the Land of Oz Posts: 1,550	Thanks. This will save me many, many keystrokes!! __________________ - old, slow, and confused ... but at least I'm inconsistent - (retired Excel 2003 user, 3.28.2008)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 21st, 2002, 02:49 PM	#1
dislecxe New Member Join Date: Mar 2002 Posts: 2	Can you write a macro to protect all sheets? I have many workbooks that have many sheets. They are veiwed by many people. I have to revise them frequently. The normal Tools, Protection, etc. on every sheet is a pain.

Mar 21st, 2002, 02:57 PM	#2
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	Following should work OK, change the password to suit :- Public Sub ProtectAll() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Protect password:="MyPass", DrawingObjects:=True, Contents:=True, Scenarios:=True Next ws End Sub

Mar 21st, 2002, 03:16 PM	#3
dislecxe New Member Join Date: Mar 2002 Posts: 2	Thanks, It works fine.

Mar 21st, 2002, 03:32 PM	#4
shades Board Regular Join Date: Mar 2002 Location: Near the Land of Oz Posts: 1,550	I have many password protected workbooks/sheets. This is a great help. Thanks. Now, can the reverse be done? Unprotecting all sheets at one time?

Mar 21st, 2002, 03:37 PM	#5
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	(Sort of) the reverse: - Public Sub DeProtectAll() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Unprotect password:="MyPass" Next ws End Sub