Sorting portion of protected worksheet

Posted by Stu on May 14, 2001 7:14 PM

I have a spreadsheet that will be used by sales reps that is protected. However, I would like them to be able to sort by columns a through L, but only rows 7 through 56. My protection password is 123. I saw in your archives a similiar example, but not being educated on VBA, I could not get it to work. Could you provide more detail on how I might accomplish this.
Thanks,
Stu

Posted by Kevin James on May 14, 2001 8:40 PM

Stu:

Just like yourself, we are all independant posters from around the world coming here to see both what we can learn and what we can answer. Personally, I was just told of this site less than a month ago.

Your message said you found an archived message. If you'll provide the message number, either myself or someone else browsing this site can help you.

(You'll find the message number in your browser's address line after you open the message.)

Kevin

Posted by David Hawley on May 14, 2001 9:26 PM

Hi Stu

Here is some code for you that will do it!

To use it Push Alt+F11 and go to Insert>Module.
Paste in the Code. Push Alt+Q to return to Excel.
Push Alt+F8, select "RestrictedSort" and click "Options". Assign a shortcut key of your choice.
Click Ok then Ok and Save.

You will notice I have written the code for a sheet called "Sales". Password being "123", no header row and to sort by A7. Change these to suit. If you have any problems at all, just let me know.

Sub RestrictedSort()
'Written by OzGrid Business Applications
'www.ozgrid.com

With Sheet("Sales")
.Unprotect password:="123"
.Range("A7:L56").Sort _
Key1:=.Range("A7"), Order1:=xlAscending, _
Header:=xlNo, Orientation:=xlSortColumns
.Protect password:="123"
End With

End Sub

Dave

OzGrid Business Applications

Posted by Stu on May 15, 2001 5:26 AM

Kevin,

That message number is 9936 andwas posted 2/9/01 by Dave.

Thanks,
Stu

Posted by Stu on May 15, 2001 6:47 AM

Dave,

I am having a problem when I go back into my spreadsheet after inserting the macro in VBA. When I push Alt+F8, and select "RestrictedSort" the "Options" button is not available and I am unable to assign a shortcut key.
What have I left out?

I am emailing you my spreadsheet if that makes it any easier.

Thanks,
Stu