Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sort in a locked spreadsheet (2003)

    I have a spreadsheet used to make schedules and it has lots of formlas. Because of this, the sheet is protected with a password.

    Occasionally, after it is done, a new hire needs to be added. I can add them at the bottom, but because it is locked, I can not sort to get them in alphabetical order. I also can not insert a line to force them in at the correct spot.

    Short of manually unprotecting the sheet, how can I get these people in the right spot?

    Thanks!
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

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

    Re: Sort in a locked spreadsheet (2003)

    In Excel 2002 and later, you can allow sorting in a protected worksheet - there's a check box for this in the Tools | Protection | Protect Sheet dialog.

  3. #3
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort in a locked spreadsheet (2003)

    The range that needs to be sorted contains cells with forumlas that are protected.

    Any way around that?
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

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

    Re: Sort in a locked spreadsheet (2003)

    Creat a macro that
    - Unprotects the sheet
    - Sorts the data
    - Re-protects the sheet
    Make the macro available through a button on the sheet, or through a custom toolbar button and/or keyboard shortcut.

  5. #5
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort in a locked spreadsheet (2003)

    Thanks. I was afraid I'd have to do that.

    I did it and it works well. The only problem is that when it unprotects it, it prompted for the password and when it re-protected it, it left the password off.

    Not a huge deal. I just removed the password and it works great. Thanks!
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

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

    Re: Sort in a locked spreadsheet (2003)

    You can specify the password both when protecting and unprotecting, e.g.

    ActiveSheet.Unprotect Password:="Topsecret"
    ...
    ActiveSheet.Protect Password:="Topsecret"

Posting Permissions

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