Error Checking in Excel
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Protecting & Unprotecting multiple sheets.

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    Anyone have a link to some code that protects and unprotects multiple sheets in one go ?


  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You cannot protect/unprotect more than one sheet at once, have you tried the following:

    Worksheets("Sheet1").Protect Password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True
    Worksheets("Sheet1").Unprotect ("password")
    Worksheets("Sheet2").Protect Password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True
    Worksheets("Sheet2").Unprotect ("password")

    Edit as needed.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  3. #3
    Board Regular shades's Avatar
    Join Date
    Mar 2002
    Location
    Near the Land of Oz
    Posts
    1,550
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Actually, someone gave me the following a few weeks ago. These two macros will do it for all sheets in a workbook.


    Sub ProtectAll()

    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
    ws.Protect password:="whatever", DrawingObjects:=True, Contents:=True, Scenarios:=True
    Next ws

    End Sub


    -----------
    Sub DeProtectAll()

    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
    ws.Unprotect password:="whatever"
    Next ws

    End Sub

    NOTE:
    "whatever" is the password (without the quotes) you supply
    _________________
    - old, slow, and confused
    ... but at least I'm inconsistent -

    [ This Message was edited by: shades on 2002-04-15 08:02 ]

  4. #4
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Shades code will work if you want to do all of the sheets of a workbook, but not if you only want to do some of the worksheets. If you want to use a for loop, then you will have to do something like the following:

    Sub ProtectWS()
    Dim ws As Worksheet
    Set WSArray = Sheets(Array("Sheet1", "Sheet3"))
    For Each ws In WSArray
    ws.Protect password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True
    Next
    end sub

    Edit as needed.

    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  5. #5
    New Member
    Join Date
    Jul 2010
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Red face Re: Protecting & Unprotecting multiple sheets.

    I am new to VBA code, so I don't fully understand. If I insert this into my VBA code, what sheet do I use - This Workbook or Module? Also, once I have it inserted, how do I run it? Sorry for the basic questions, but until I get training (sometime in September) I have to go this route.

  6. #6
    MrExcel MVP
    Moderator

    Tracy's new best bud
    Jon von der Heyden's Avatar
    Join Date
    Apr 2004
    Location
    London, UK
    Posts
    10,615
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Protecting & Unprotecting multiple sheets.

    Hi and welcome to the board!

    The code you refer to should sit within a standard module. You can either run it by hitting ALT+F8 and then choosing the macro from the dialog that opens, or you can attach the macro to a forms button in one of your sheets.
    Regards,
    Jon von der Heyden

    Posting guidelines | Forum rules | FAQs
    English is a weird language. It can be understood through tough thorough thought, though!

  7. #7
    New Member
    Join Date
    Jul 2010
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Protecting & Unprotecting multiple sheets.

    Thank you so much for your assistance! This worked like a charm!!

  8. #8
    New Member
    Join Date
    Jul 2010
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Protecting & Unprotecting multiple sheets.

    I'm sorry, one last question. Can I modify this code to unprotect the worksheets as well? I tried the following:
    Sub UnProtectWS()
    Dim ws As Worksheet
    Set WSArray = Sheets(Array("Sheet1","Sheet3"))
    For Each ws In WSArray
    ws.Unprotect Password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True
    Next
    End Sub

    However, I get a Compile Error:Named argument not found, and the "DrawingObjects:" is highlighted. What am I doing wrong?

    Thank you ~ Agieseke

  9. #9
    MrExcel MVP
    Moderator

    Tracy's new best bud
    Jon von der Heyden's Avatar
    Join Date
    Apr 2004
    Location
    London, UK
    Posts
    10,615
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Protecting & Unprotecting multiple sheets.

    Those arguments are not available when you unprotect, only when you protect. So it would be:

    Code:
    Sub UnProtectWS()
        Dim ws As Worksheet
        Set WSArray = Sheets(Array("Sheet1","Sheet3"))
        For Each ws In WSArray
             ws.Unprotect Password:="password"
        Next
    End Sub
    Regards,
    Jon von der Heyden

    Posting guidelines | Forum rules | FAQs
    English is a weird language. It can be understood through tough thorough thought, though!

  10. #10
    New Member
    Join Date
    Sep 2010
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Protecting & Unprotecting multiple sheets.

    I am using the above macros and have been successful in protecting multiple worksheets with a password, but I can't seem to protect them in a way that allows me to continue to use the Pivot Table Reports. Is there additional language that I can include that will select only "Use Pivot Table Reports" from the list on the "Protect Sheet" screen?

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
  •