Results 1 to 5 of 5
  1. #1
    5 Star Lounger ibe98765's Avatar
    Join Date
    Aug 2001
    Location
    Bay Area, California, USA
    Posts
    966
    Thanks
    19
    Thanked 4 Times in 4 Posts

    Passwords (Office 2002 SP2)

    I have a number of spreadsheets with 5-8 sheets that need to be password protected. In order to make mods to these sheets, I need to manually go to each sheet and click through tools-protection-unprotect sheet. Then I need to reverse this to re-protect the sheets.

    Is there some freeware or a macro floating around that will allow me to simplify this process. I'd like to be able to remove all passwords on all sheets in a workbook at once or set a password for all sheets at once.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passwords (Office 2002 SP2)

    Something like this:

    Option Explicit

    Sub ProtAll()
    Dim oSheet As Worksheet
    For Each oSheet In ActiveWorkbook.Worksheets
    oSheet.Protect password:="test", DrawingObjects:=True, contents:=True, Scenarios:=True
    Next
    End Sub
    Sub unProtAll()
    Dim oSheet As Worksheet
    For Each oSheet In ActiveWorkbook.Worksheets
    oSheet.Unprotect password:="test"
    Next
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Passwords (Office 2002 SP2)

    Here are two macros. They assume that the password is the same for all sheets within a workbook. If you place these macros in your Personal.xls, you can use them in all your workbooks; you can even assign the macros to keyboard shortcuts or custom toolbar buttons.

    Sub ProtectAll()
    Dim sh As Worksheet, ch As Chart
    Dim strPw As String
    strPw = InputBox("Enter Password")
    For Each sh In ActiveWorkbook.Worksheets
    sh.Protect strPw, True, True, True
    Next sh
    For Each ch In ActiveWorkbook.Charts
    ch.Protect strPw, True, True
    Next ch
    Set sh = Nothing
    Set ch = Nothing
    End Sub

    Sub UnProtectAll()
    Dim sh As Worksheet, ch As Chart
    Dim strPw As String
    strPw = InputBox("Enter Password")
    For Each sh In ActiveWorkbook.Worksheets
    sh.Unprotect strPw
    Next sh
    For Each ch In ActiveWorkbook.Charts
    ch.Unprotect strPw
    Next ch
    Set sh = Nothing
    Set ch = Nothing
    End Sub

  4. #4
    Star Lounger
    Join Date
    Mar 2003
    Location
    Sydney, New South Wales, Australia
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passwords (Office 2002 SP2)

    Navigator Utilities at www.robbo.com.au can do just what you want. The Sheet Navigator provides a list of all sheets, including hidden and "very" hidden sheets. Just select the sheets, or click Select All, then click Protect, or click unprotect - and if you forget the password, it will be broken for you. Any feedback greatly appreciated. regards

  5. #5
    5 Star Lounger ibe98765's Avatar
    Join Date
    Aug 2001
    Location
    Bay Area, California, USA
    Posts
    966
    Thanks
    19
    Thanked 4 Times in 4 Posts

    Re: Passwords (Office 2002 SP2)

    Thanks all! The Nav Utilities look interesting and I will try them out since they solve more than 1 problem.

Posting Permissions

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