Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unprotecting Many Sheets (Excel 2002)

    Is there a way to unprotect all the sheets in a workbook with a macro? I have a file with 20 protected sheets and often have to unprotect each one to fix user errors. Each sheet has the same password.

    TIA
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Unprotecting Many Sheets (Excel 2002)

    Try the following

    <code>Sub RemovePassword()
    Dim i As Long
    Dim strPasswd As String
    strPasswd = InputBox("Enter password to unlock sheets")
    For i = 1 To Sheets.Count
    Worksheets(i).Unprotect Password:=strPasswd
    Next i
    End Sub</code>
    <code></code>

  3. #3
    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

    Re: Unprotecting Many Sheets (Excel 2002)

    You should not dimension oSheet as a worksheet if you are going thru the sheets collection.
    Use:
    <pre>Dim oSheet</pre>

    to be able to go thru all sheets
    Or
    <pre>For Each oSheet In WorkSheets</pre>


    if you only want to go thru worksheets (then you can dim as worksheet.

    If you do not and have any chart sheets you will get a type mismatch error.

    Steve

  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

    Re: Unprotecting Many Sheets (Excel 2002)

    See my note to Legare in <post#=387933>post 387933</post#>

    The sheets.count might be larger the worksheets index.

    You need to use:
    <pre> For i = 1 To Sheets.Count
    Sheets(i).Unprotect Password:=strPasswd
    Next i</pre>


    to work on all sheets
    or
    <pre> For i = 1 To Worksheets.Count
    Worksheets(i).Unprotect Password:=strPasswd
    Next i</pre>

    to just look at worksheets. If you have chartsheets you will get a runtime error

    Steve

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unprotecting Many Sheets (Excel 2002)

    Legare,
    This works great!

    Thanks.
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unprotecting Many Sheets (Excel 2002)

    Tony,
    This works great too - and has the added advantage of being able to use it for other worksheets.

    Thanks alot!
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unprotecting Many Sheets (Excel 2002)

    Something like this should do what you asked:

    <pre>Public Sub UnprotectAllSheets()
    Dim oSheet As Worksheet
    For Each oSheet In WorkSheets
    oSheet.Unprotect ("sheetpassword")
    Next oSheet
    End Sub
    </pre>

    Legare Coleman

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unprotecting Many Sheets (Excel 2002)

    Yup, I meant "In Worksheets". I have corrected my post. Thanks for catching that.
    Legare Coleman

Posting Permissions

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