Excel 2007: How to protect multiple sheets at once

Excel worksheets are sometimes protected to prevent formulas or formatting from being accidentally cleared or changed.

It can be time consuming when there are many worksheets and when a protect password is included.

To save time you could set up a macro to protect multiple worksheets at once. Then you can press one button or a shortcut key to protect the worksheet.

To password protect all worksheets in a workbook in Excel 2007 write the following:

Sub ProtectSheets()

Dim wsheet As Worksheet
For Each wsheet In Worksheets

wsheet.Select
ActiveSheet.Protect Password:="abc"

Next wsheet

End Sub

Similarly a macfro can be written to unprotect worksheets quickly:

Sub UnProtectSheets()

Dim wsheet As Worksheet

For Each wsheet In Worksheets

wsheet.Select
ActiveSheet.Unprotect Password:="abc"

Next wsheet

End Sub

Protecting a single worksheet

To password protect a single worksheet write the following procedure into a Module of the VBE (Visual Basic Editor)

Sub Protectsheet()
ActiveSheet.Protect Password:="abc"
End Sub

This macro can then be run in any worksheet, or one in an open workbook, that that you wish to protect with the password abc.

See the links below for other ways to password protect worksheets.

Related forum posts:

Protecting Multiple Worksheets

Is there a fn to protect multiple worksheets at once rather than going into each one to protect them?
Read forum post

macros

can single macro apply to all worksheets of a workbook without activating for each worksheet. i.e. activate once for entire workbook
Read forum post

Share And Share Alike - Use Excel To Collaborate As A Winning Team!

One beneficial program which promotes virtual teamwork in real time is Microsoft Excel. Its performance is only limited by the person who uses it. This is a great tool for keeping information and yourself organized. On a professional level, it can be used to distribute data between departments. Follow the instructions below to learn just how easy it is to share a workbook.
Read article

Trust Excel To Protect Your Data

Whether you are working with sales projections or salary figures, it's important to use a reliable and trustworthy program to compile spreadsheet documents. Excel 2010 offers just that with the added security of its Trust Center to protect all of your confidential and sensitive data.
Read article

How Sharing Your Excel Data Can Help Your Business Flourish

This article demonstrates the benefits of Excel's functions to incorporate key data into into other Office applications, such as Word documents and PowerPoint presentations, to improve business outcomes.
Read article

Training courses