Power Query Course in Spanish
Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

Thread: Protecting & Unprotecting multiple sheets.

  1. #11
    Board Regular
    Join Date
    Nov 2004
    Posts
    915
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Protecting & Unprotecting multiple sheets.

    Dear All

    Not sure who will see this but I have used the following code on a test workbook which works fine:

    Sub ProtectWS()
    Dim ws As Worksheet
    Set wsArray = Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7"))
    For Each ws In wsArray
    ws.Protect Password:="fred"
    'ws.Range("A1").Select
    Next
    End Sub

    Sub UnProtectWS()
    Dim ws As Worksheet
    Set wsArray = Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7"))
    For Each ws In wsArray
    ws.Unprotect Password:="fred"
    Next
    End Sub

    However in a much larger project the only way it would work is to define Dim wsArray as a Variant.

    We have checked the code and there are no other arrays. Is there something else that we should be looking for?

    Any advice would be appreciated

  2. #12
    New Member
    Join Date
    Jan 2014
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Protecting & Unprotecting multiple sheets.

    Quote Originally Posted by shades View Post
    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 ]
    Shades,

    First of all, this code is awesome! Thank you. Before I added this code if I wanted to individually unprotect a sheet, I didn't need a password. Now that I added this code however, a password is now required to unlock individual pages (which automatically became "whatever"). Is there a variant of this code that would not require a password to unlock each page individually nor by using the macros?

  3. #13
    New Member
    Join Date
    Jan 2014
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Protecting & Unprotecting multiple sheets.

    Quote Originally Posted by Jasuan View Post
    Shades,

    First of all, this code is awesome! Thank you. Before I added this code if I wanted to individually unprotect a sheet, I didn't need a password. Now that I added this code however, a password is now required to unlock individual pages (which automatically became "whatever"). Is there a variant of this code that would not require a password to unlock each page individually nor by using the macros?
    I figured this out, but can't figure out how to delete my Reply.

    When I protect the worksheets, I need to be able to select a few options of the "Allow all users of this worksheet to:" box.

    The options I need to have checked are: "Edit Objects," "Format Cells" and "Select Unlocked Cells."

    Thank you all in advance.

  4. #14
    Board Regular
    Join Date
    Aug 2009
    Posts
    1,335
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Protecting & Unprotecting multiple sheets.

    I'm using these two macros. My array has 20 sheets in it. The only change I made, apart from the array contents, is to remove 'password="fred"' because I don't have a password on the protected sheets.

    unprotectws works fine but when I run protectws I get the error 'runtime error 1004 method 'protect' of object '_worksheet' failed'

    Can anyone spot the problem, please?

    Quote Originally Posted by lapta301 View Post
    Dear All

    Not sure who will see this but I have used the following code on a test workbook which works fine:

    Sub ProtectWS()
    Dim ws As Worksheet
    Set wsArray = Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7"))
    For Each ws In wsArray
    ws.Protect Password:="fred"
    'ws.Range("A1").Select
    Next
    End Sub

    Sub UnProtectWS()
    Dim ws As Worksheet
    Set wsArray = Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7"))
    For Each ws In wsArray
    ws.Unprotect Password:="fred"
    Next
    End Sub

    However in a much larger project the only way it would work is to define Dim wsArray as a Variant.

    We have checked the code and there are no other arrays. Is there something else that we should be looking for?

    Any advice would be appreciated

  5. #15
    MrExcel MVP
    Moderator

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

    Default Re: Protecting & Unprotecting multiple sheets.

    Have you tried to debug which sheet in the array it is breaking on?
    Regards,
    Jon von der Heyden

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

  6. #16
    Board Regular
    Join Date
    Aug 2009
    Posts
    1,335
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Protecting & Unprotecting multiple sheets.

    It breaks on the first pass. I just managed to find the problem.

    both macros are fine but the problem was what i was doing in between. I was unprotecting, grouping the sheets to perform a common change then reprotecting.

    The code in between left the sheets grouped and that caused the error.

    thanks for your help.

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
  •