Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    protect and unprotect (Office 97)

    Is there a way to protect and unprotect all the worksheets at once? I would have thought that 'protect workbook' would be the way to do it, but it doesn't--I have to go to each page (about 40) and turn it on or off invididually every time

  2. #2
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: protect and unprotect (Office 97)

    Protect workbook and protect worksheet are two different things. "Worksheets" refers to each individual item within the workbook.

    You can unprotect all of the individual worksheets from VBA code as a result of an event such as workbook open, worksheet activate, workbook before close and such.

    It may be more efficient to unprotect individual cells or ranges within each sheet if you are entering data rather than the entire sheet.

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: protect and unprotect (Office 97)

    I haven't got excel in front of me - but would this work?
    Group all of the sheets in the workbook and then protect the sheets. Because they are grouped - they should all be protected together.

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: protect and unprotect (Office 97)

    Unfortunately, No, grayed out in group mode.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: protect and unprotect (Office 97)

    would love to know if you come up with a way around the problem. Have run into it myself. The only shorcut I can come up with is to copy the password onto the clipboard and paste it as I lock/unlock each sheet.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: protect and unprotect (Office 97)

    I guess simplest is these two subs:

    Option Explicit

    Sub ProtAll()
    Dim MySheet As Worksheet
    For Each MySheet In ThisWorkbook.Worksheets
    MySheet.Protect Password:="MyPass"
    Next
    End Sub
    Sub UnProtAll()
    Dim MySheet As Worksheet
    For Each MySheet In ThisWorkbook.Worksheets
    MySheet.Unprotect Password:="MyPass"
    Next
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: protect and unprotect (Office 97)

    Catherine,

    You cannot protect more than one sheet at a time interactively, I suppose because you might want different forms of protection per sheet and it would be considered imprudent to use the same password on all sheets, though I suspect most people would, including myself.

    Protecting the workbook does not protect the sheet contents other than by protecting the sheet itself against deletion.

    I suppose you could have some code do a For Each Sheet loop.

    Andrew C

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: protect and unprotect (Office 97)

    One either protects an entire sheet, or not. Cell protection is invoked once the sheet is protected. By default all cells are locked, which only takes effect after protecting the sheet. So one:
    - Unlocks the cells one wants the user to have access to
    - protects the sheet
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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