Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    Jan 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting Protected Worksheet (Excel 2000)

    We use an Excel spreadsheet for field employees to request expense reimbursements. They fill out the spreadsheet and email it for approval and processing. The cells where we intend for users to enter their expenses are unlocked and then the worksheet is protected to avoid inadvertant deletion of the many formulas. Employees have requested the ability to enter their expenses in any order and then sort them by date. I can't figure out a way to accomplish this since the sort function is unavailable once the worksheet is protected (even though the cells in the area I want to sort are unlocked). Excel 2002 provides the ability to choose what users can and can't do when the worksheet is locked (including sort), but no such luck with Excel 2000. Can anyone think of a work-around? Many thanks.

    bigfilo

  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

    Re: Sorting Protected Worksheet (Excel 2000)

    You could add a button to run a macro to sort.

    The macro would unprotect worksheet, sort the necessary columns by date and any sec or tert sort, then reprotect

    Steve

  3. #3
    New Lounger
    Join Date
    Jan 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Protected Worksheet (Excel 2000)

    I tried using a macro, but if the worksheet is protected with a password (which it needs to be to avoid it being unprotected and changed), the macro simply brings up the Unprotect Sheet dialogue box when it gets to that step and the user has to enter the password (which they won't know). Your suggestion would work if the worksheet was protected without a password, but that would defeat the purpose of protecting it in the first place.

  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: Sorting Protected Worksheet (Excel 2000)

    Add the password in the macro (change sheet name and password as appropriate.

    sheets("sheet1").unProtect Password := "drowssap"

    sheets("sheet1").Protect Password := "drowssap"

    Steve

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Sorting Protected Worksheet (Excel 2000)

    You can write a macro that unprotects and reprotects the sheet without revealing the password. At it's simplest:

    WorkSheets("Name").UnProtect Password:="test"
    Range("RangeName).Sort ...
    WorkSheets("Name".Protect Password:="test"

    If this doesn't help, post the code and we can work on it for you.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    New Lounger
    Join Date
    Jan 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Protected Worksheet (Excel 2000)

    Steve and John - thanks! I don't know MVB, but I tried your code and I think it will work. One question - how do you prevent someone from editing the macro and learning the password? BTW, this is the first time I've visited Woody's Lounge, but I'm very impressed. Obviously some very knowledgeable loungers. I have been trying to solve this issue on and off for a couple of days. Thanks for the help.

    Phil
    romneyp@edenbio.com

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

    Re: Sorting Protected Worksheet (Excel 2000)

    Excel passwords are not very secure, and there are many programs avaliable on the WWW that will crack most of them. You can protect your code with a password, but a determined user could crack either or both without a great deal of difficulty.

    To protect your code, go to the VBE (Visual Basic Editor). In the Project Explorer right click on the module that contains your code. Select VBA Project Properties on the pop up menu. Click on the Protection tab in the dialog box and protect the code from viewing.
    Legare Coleman

  8. #8
    New Lounger
    Join Date
    Jan 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Protected Worksheet (Excel 2000)

    Legare:

    I agree with you regarding Excel security. I'm just trying not to make it too easy for those determined few who want to modify the spreadsheet. Should the instructions you gave me prevent me from editing the macro (and this seeing the Excel password)? I've tried it several times and I am still able open to edit the macro after turning on the protection. I must be doing something wrong. Any other hints? Thanks.

    Phil

  9. #9
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Protected Worksheet (Excel 2000)

    If you have not closed the spreadsheet, Excel will cache the password, so you don't have to re-enter it every time you want to edit code.

    Close the file and then reopen it and try and get into the code. It will prompt you for the password.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  10. #10
    New Lounger
    Join Date
    Jan 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Protected Worksheet (Excel 2000)

    That was the trick. Finally, I have recorded a macro that summarizes the data for accounting once the expense report has been submitted for processing. I want to put a button on the worksheet and link the "summarization" macro to that button, but I want only accounting to be able to execute that particular macro. Is there a way to set it up so that when someone clicks on that button, the macro would execute only if they entered the correct password (which only accounting would have)? Thanks.

  11. #11
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Protected Worksheet (Excel 2000)

    bigfilo,

    I cobbled together a macro that appears to work. This is by NO means the best that can be done, I just am not very good at this -YET.

    Chuck
    Attached Files Attached Files
    Chuck Reimer
    I'm from the Government and I'm here to help...

  12. #12
    New Lounger
    Join Date
    Jan 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Protected Worksheet (Excel 2000)

    Thanks, Chuck. I'll play around with it. I need to sign up for Visual Basic class.

    Phil

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Sorting Protected Worksheet (Excel 2000)

    I'd recommend you start by getting and working through some books rather than a class. See <!post=this,180035>this<!/post> thread for a recent discussion on Excel VBA books.
    -John ... I float in liquid gardens
    UTC -7ąDS

  14. #14
    New Lounger
    Join Date
    Jan 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Protected Worksheet (Excel 2000)

    Thanks, John. There didn't seem to be any "clear winners," but I ordered Visual Basic 6 From the Ground Up.

    Phil

Posting Permissions

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