Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Protecting / Unprotecting multiple worksheets

  1. #1
    New Member
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is there a way to protect and/or unprotect an entire group of worksheets? I have 25 in one workbook. Protect Workbook does not do what I want it to, unless I've missed something. I want only certain cells accessable to the users. But occasionally I have to unprotect them all to change something.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sub UnprotectAllSheets()
    Dim s
    For Each s In Worksheets
    s.Unprotect password:="YourPassword"
    Next s
    End Sub

    Sub ProtectAllSheets()
    Dim s
    For Each s In Worksheets
    s.Protect password:="YourPassword"
    Next s
    End Sub
    The password would need to be the same throughout...
    Hope this helps,
    Tom

  3. #3
    New Member
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Pardon my 'newbieness', but is that a Macro? I don't want to use a password. Do I need the password part?

    I was afraid it would be something complex like this, but if it is a Macro, well, I guess I now learn how to use them. At least I have the formula.

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    No need for a password...

    Sub UnprotectAllSheets()
    Dim s
    For Each s In Worksheets
    s.Unprotect
    Next s
    End Sub

    Sub ProtectAllSheets()
    Dim s
    For Each s In Worksheets
    s.Protect
    Next s
    End Sub

    This macro is nothing more than a repetitive action you would need to perform on each sheet.

    Tom

  5. #5
    New Member
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Tom. I was afraid it was going to be the 'M' word. I'm leafing through my Excel How To book as we speak. Will get back to you with the results. Thxs again.

  6. #6
    New Member
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Most of you will probably cringe when you see how I accomplished this, but the end justifies the means I guess.

    I assume that code goes into some visual basics editor or something and I haven't a clue how to navigate that. Opened Visual Basics Editor but couldn't figure out how to get passed the blank screen. (no sheet of paper to work on).

    So I recorded a macro, which turned out to be very simple to do, except it wouldn't work if I added worksheets or changed the name on the tab. So I clicked on edit, deleted the code (if that's the proper word) and pasted the code you wrote, Tom. That worked.

    If there is an easier way to accomplish this that doesn't task your time too much, feel free to enlighten me. I do have one specific question though. Is the code in a Macro case-sensitive? Feel free to correct me if I'm using the wrong terms.

Some videos you may like

User Tag List

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
  •