Results 1 to 5 of 5
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    How to cicling all protected sheets in a workbook in one shot?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How about this:
    Code:
    Option Explicit
    Sub UnprotectAll()
      Dim sht As Variant
      For Each sht In ActiveWorkbook.Sheets
    	sht.Unprotect
      Next
    End Sub
    Steve

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='sdckapr' post='767747' date='27-Mar-2009 14:09']How about this:
    Code:
    Option Explicit
    Sub UnprotectAll()
      Dim sht As Variant
      For Each sht In ActiveWorkbook.Sheets
    [tab][/tab]sht.Unprotect
      Next
    End Sub
    Steve[/quote]

    WOW!!! Work.

    but to understand sht As variant, why?

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Because there are different types of sheets, there are worksheets, some may be chart sheets, some even could be macro sheets.

    If you only want to work on only the worksheets you could use:

    Code:
    Option Explicit
    Sub UnprotectWorksheets()
      Dim wks As Worksheet
      For Each wks In ActiveWorkbook.Worksheets
    	wks.Unprotect
      Next
    End Sub
    Steve

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    Steves code will work if there are no passwords assigned to the sheets.

    If you do have the same password assigned to the protection command, then wks.Unprotect "password" will unprotect all the sheets using the password to unlock them all

    If you have a different password assigned to the protection command for each sheet, then wks.Unprotect "password" will not work as it will assign the wrong password to the next sheet.

    You may then have to resort to the traditional manual method!
    Regards,
    Rudi

Posting Permissions

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