Sorting Protected Worksheet (Excel 2000)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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

sheets(“sheet1”).unProtect Password := “drowssap”

sheets(“sheet1”).Protect Password := “drowssap”

Steve

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Phil

Reply | Quote

I’d recommend you start by getting and working through some books rather than a class. See this thread for a recent discussion on Excel VBA books.

Reply | Quote

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

Phil

Reply | Quote