Excel 2007: How to protect multiple sheets at once

TrustPilot

starstarstarstarstar Excellent

Forum home » Topics » Excel 2007: How to protect multiple sheets at once

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

 

Related articles

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

 

 

Live dates & prices

 

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


TrustPilot
TrustScore 4.9 / 5       1108 reviews