Excel - Sorting In A Protected Worksheet
Thread Starter
Joined: Sep 1998
Posts: 513
Likes: 0
From: Sydney, Australia
Excel - Sorting In A Protected Worksheet
I've written an Excel spreadsheet that contains a macro to sort a range of cells. Everything works as intended - so far so good.
I want to protect my intellectual property and I can do that by Protecting the worksheet. However you can't do the sort unless the worksheet is unprotected. That's easy IF YOU DON'T USE A PASSWORD. You can unprotect the sheet first thing in the macro, sort it, then re-protect it last thing.
Protection without a password is basically useless - all it does is inhibit inadvertent alteration of formulas. Anyone can defeat it from the menu with Tools|Protection|Unprotect - in fact that is what my current macro does prior to sorting, then restores protection in a similar way afterwards. Once Protection is off, of course, anyone can access the whole design of the spreadsheet and rip you off!
What I want to do is have a password-protected worksheet that allows sorting of a range without removing the protection first.
When you apply protection one of the check-boxes is Allow users of this worksheet to Sort. I've checked that, but it doesn't solve my problem - the macro won't run and the debugger reports that protected cells are the problem.
I'm missing something somewhere, can't find it in my searches of both Help and MS Knowledgebase, so if anyone has the answer I'd sure appreciate it.
Cheers
AA
I want to protect my intellectual property and I can do that by Protecting the worksheet. However you can't do the sort unless the worksheet is unprotected. That's easy IF YOU DON'T USE A PASSWORD. You can unprotect the sheet first thing in the macro, sort it, then re-protect it last thing.
Protection without a password is basically useless - all it does is inhibit inadvertent alteration of formulas. Anyone can defeat it from the menu with Tools|Protection|Unprotect - in fact that is what my current macro does prior to sorting, then restores protection in a similar way afterwards. Once Protection is off, of course, anyone can access the whole design of the spreadsheet and rip you off!
What I want to do is have a password-protected worksheet that allows sorting of a range without removing the protection first.
When you apply protection one of the check-boxes is Allow users of this worksheet to Sort. I've checked that, but it doesn't solve my problem - the macro won't run and the debugger reports that protected cells are the problem.
I'm missing something somewhere, can't find it in my searches of both Help and MS Knowledgebase, so if anyone has the answer I'd sure appreciate it.
Cheers
AA
Joined: Oct 2000
Posts: 779
Likes: 0
From: Sunny Sussex
Congrats on spotting yet another shortcomig in excel. The more you use it, the more you bump up against things it won't do.
The obvious answer is write a vb script to allow you to protect & enable sorting, but easier said than done I'm afraid.
The obvious answer is write a vb script to allow you to protect & enable sorting, but easier said than done I'm afraid.
Joined: Mar 2003
Posts: 195
Likes: 2
From: Aus
AA.
You need to add the command
ActiveSheet.Unprotect "xxxx"
at the start of your sorting macro
and the command
ActiveSheet.Protect "xxxx"
at the end. xxx is the password for the sheet protection.
Then to stop people from seeing your password you need to protect the VB code.
In Excel 2000 this is done from the VB editor. Rt click on the Modules folder and select VBAProject Properties On the Protection tab tick the "Lock project for viewing" and enter/re-enter a password.
XP may be slightly different.
Let me know if this helps
MAx
You need to add the command
ActiveSheet.Unprotect "xxxx"
at the start of your sorting macro
and the command
ActiveSheet.Protect "xxxx"
at the end. xxx is the password for the sheet protection.
Then to stop people from seeing your password you need to protect the VB code.
In Excel 2000 this is done from the VB editor. Rt click on the Modules folder and select VBAProject Properties On the Protection tab tick the "Lock project for viewing" and enter/re-enter a password.
XP may be slightly different.
Let me know if this helps
MAx
