Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jun 2001
    Location
    Burlington, Ontario, Canada
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sort on Protected Sheet (Excel 97 SR1)

    Greetings all.
    I just want to make sure for a colleague, but he is trying someway to protect a sheet yet still be able to use Data, Sort. Once the sheet is protected, Data,Sort is unavailable.

    The goal is to have a sheet with data that the end users can sort anyway they want but not alter the data itself. We had thought about saving as read-only and but was nixed because it wouldn't stop them from saving the file somewhere else and corrupting the original data later on. I even suggested going to pivot tables but there was concern on the users not understanding the capabilities.
    So is there a way to sort on a protected sheet or do we use another method? Your assistance is appreciated.

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort on Protected Sheet (Excel 97 SR1)

    John,

    Have a look at <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=xl&Number=52235&page=&view= &sb=&o=&vc=1>this thread</A>.

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort on Protected Sheet (Excel 97 SR1)

    I had to let users use the tracing feature which is not accessible on a protected sheet (like sort) and I ended up writing code which unprotected the sheet but then used the selection_change() event to monitor which cell the user was activating. If the cell/s was in my "protected" zone (one which I don't want them to change) then I issued a warning and did undo (application.undo like ^Z). This gave them the edit ability on some cells but not others and still allowed them to use a "protected" feature (sort in your case, tracing in mine). When they leave the worksheet, I'd protected it again (worksheet_deactivate() event). I also used this to hide formulas in certain areas on a sheet.

    I read the thread listed by another person and it will work but it doesn't prevent users from changing cells before they sort. The other thing you can do is provide a button which querys for the range to be sorted then in VBA do the sort yourself.

    Deb <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

Posting Permissions

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