Excel Tips and Tricks – Mass Protect Sheets

Oct. 20, 2011 | by Andrew Ball

The Research and Insight team spend many hours with Excel, wrestling with it to get the best results we can.

As we do, we all come across little things that make our lives that bit easier. In theory if I find it useful, then someone else might too so I am going to share some tips and tricks here on connect.

So, to start off – Protecting Sheets. Excel allows you to protect sheets with a password, to prevent data being entered into the incorrect cells. The problem is that worksheets can only be locked or unlocked one sheet at a time. So, we have a macro that you can add to your version of excel to enable mass locking and unlocking of sheets.

Step 1. Download the 3 files in this folder ProtectSheets

Step 2. Unhide your “PERSONAL.XLSB” excel workbook

a. Go to the View section of the ribbon. Click on “Unhide”

Excel

b. Select the personal workbook

Personal workbook

Step 3. Import the code to your personal workbook

a. Press Alt+F11 to bring up the Visual Basic window

b. Right-click on VBAProject (PERSONAL.XLSB) and select “Import File”

Right click on VBAproject

c. Select “ChangeProtectionOnSheets.frm” to be imported

d. Repeat for “PasswordProtectSheets.bas”

e. Close VB Editor

Step 4. Add a shortcut to use the new macro

a. Select to customise your Quick Access Toolbar (if you want the macro shortcut here). Select ‘More Commands..’

More commands

b. Alter list to be ‘Macros’

Macros

c. Add “PERSONAL.XLSB!PasswordProtectSheets”

Excel menu

d. You can then modify the icon if you wanted (Personally I use a lock icon)

Step 5. Hide your “PERSONAL.XLSB” excel workbook

a. Go to the View section of the ribbon. Click on “Hide”

Hide

Clicking on the new icon displays the following form, which allows you to protect or unprotect all the sheets in your workbook at once.

Protect sheets

Be Sociable, Share!

    Comment (1)

    • Rachel

      Very useful!Oct 24, 2011 05:58 pm

     
    Please note: the opinions expressed in this post represent the views of the individual, not necessarily those of iCrossing.

    Post a comment

    SUBSCRIBE