Results 1 to 3 of 3

Protect multiple worksheets excel 2007

This is a discussion on Protect multiple worksheets excel 2007 within the Excel Questions forums, part of the Question Forums category; When I select multiple worksheets in excel 2007 the protect worksheet button is greyed out. How do I protect multiple ...

  1. #1
    New Member
    Join Date
    May 2008
    Posts
    14

    Default Protect multiple worksheets excel 2007

    When I select multiple worksheets in excel 2007 the protect worksheet button is greyed out. How do I protect multiple worksheets in excel 2007 without having to do each one individually.

  2. #2
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,262

    Default Re: Protect multiple worksheets excel 2007

    Welcome to the Board!

    You can use VBA:

    Public ws As Worksheet

    Sub ProtectAll()
        For Each ws In ActiveWorkbook.Worksheets
            ws.Protect
        Next ws
    End Sub

    Sub UnProtectAll()
        For Each ws In ActiveWorkbook.Worksheets
            ws.Unprotect
        Next ws
    End Sub


    Hope that helps,
    Smitty

    Every once in a while, there's a sudden gust of gravity...

    Check out my new book at the Mr. Excel Bookstore!

    Mr. Excel HTML Maker - Post a shot of your sheet

  3. #3
    Board Regular Mark F's Avatar
    Join Date
    Jun 2002
    Location
    Biggleswade, Beds , England
    Posts
    452

    Default Re: Protect multiple worksheets excel 2007

    Code:
     
     Public Sub ProtectGroupedSheets()
    '========================================================================
    ' THIS PROTECTS GROUPED SHEETS
    '========================================================================
    'Const csPASSWD As String = "password"
            Dim MySheets As Sheets
            Dim actSheet As Worksheet
            Dim wkSht As Worksheet
            Set actSheet = ActiveSheet
            Set MySheets = ActiveWindow.SelectedSheets
            actSheet.Select
            For Each wkSht In MySheets
                wkSht.Protect 'Password:=csPASSWD ' Can also use Unprotect
            Next wkSht
            actSheet.Select
            MySheets.Select False
        End Sub
    Another option, if you need to protect specific sheets only.

    Not sure if this will meet your needs.

    Select the specific sheets you want to protect then run the code. You can put password protection in if required

    Mark

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com