Question about sorting in protected worksheet

[SJC]

I have created a spreadsheet to be used by others in which they will input
their monthly data. So many of you have helped me put this thing together--I
am extremely grateful to you all. My hopefully last question is regarding
protection. I have protected all of the worksheets, as the last time we did
this sort of spreadsheet, novice users ended up deleting and changing all of
the formulas and links. However, I realize that you cannot use the 'sort'
function while the worksheet is protected. They will need to sort their
data. Is there a way to protect a worksheet while also keeping the sort
function?

Thanks for any insight!

[Cesar Zapata]

If you are using excel 2003 and may be xp... when you protect you have
the option to allow sorting. If you have lesser versions then I think
you gonna have to go VBA.

[SJC]

I am using Excel 2000, so do you have any idea on how I can program this in?

"Cesar Zapata" wrote:

> If you are using excel 2003 and may be xp... when you protect you have
> the option to allow sorting. If you have lesser versions then I think
> you gonna have to go VBA.
>
>

[DCSwearingen]

Create VBA similar to the following and put a button on the worksheet to run the SortAscending or SortDescending macro below.

A Macro button can be inserted using View/Toolbars/Forms. The wizard will come up asking which macro, so be sure to have them created ahead of time.

Be sure to use your range and sort key. If you want to use more than one sort key, enter
*Sort Key 2:=Range("B2"), Order2:=xlDescending


Sub SortDescending()
UnProtectSheet
Range("A2:D34").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
ProtectSheet
End Sub


Sub SortAscending()
UnProtectSheet
Range("A2:D34").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
ProtectSheet
End Sub


Sub UnProtectSheet()
ActiveSheet.Unprotect ("Password")
End Sub


Sub ProtectSheet()
ActiveSheet.Protect Password:="Password"
End Sub

[Gord Dibben]

SJC

Sub sortprotected()
ActiveSheet.Unprotect Password:="justme"

'your sort code which you get from the macro recorder

ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub

Note: for users not to have access to the password, you must protect the VBA
Project, which could be cracked by a determined user.


Gord Dibben Excel MVP

On Thu, 24 Mar 2005 11:15:02 -0800, SJC <SJC@discussions.microsoft.com> wrote:

>I am using Excel 2000, so do you have any idea on how I can program this in?
>
>"Cesar Zapata" wrote:
>
>> If you are using excel 2003 and may be xp... when you protect you have
>> the option to allow sorting. If you have lesser versions then I think
>> you gonna have to go VBA.
>>
>>

[SJC]

Yes, that will do it--thank you so much!!

"Gord Dibben" wrote:

> SJC
>
> Sub sortprotected()
> ActiveSheet.Unprotect Password:="justme"
>
> 'your sort code which you get from the macro recorder
>
> ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _
> Contents:=True, Scenarios:=True
> End Sub
>
> Note: for users not to have access to the password, you must protect the VBA
> Project, which could be cracked by a determined user.
>
>
> Gord Dibben Excel MVP
>
> On Thu, 24 Mar 2005 11:15:02 -0800, SJC <SJC@discussions.microsoft.com> wrote:
>
> >I am using Excel 2000, so do you have any idea on how I can program this in?
> >
> >"Cesar Zapata" wrote:
> >
> >> If you are using excel 2003 and may be xp... when you protect you have
> >> the option to allow sorting. If you have lesser versions then I think
> >> you gonna have to go VBA.
> >>
> >>
>
>

[Gord Dibben]

Thanks for the feedback.

Gord

On Thu, 24 Mar 2005 12:51:06 -0800, SJC <SJC@discussions.microsoft.com> wrote:

>Yes, that will do it--thank you so much!!
>
>"Gord Dibben" wrote:
>
>> SJC
>>
>> Sub sortprotected()
>> ActiveSheet.Unprotect Password:="justme"
>>
>> 'your sort code which you get from the macro recorder
>>
>> ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _
>> Contents:=True, Scenarios:=True
>> End Sub
>>
>> Note: for users not to have access to the password, you must protect the VBA
>> Project, which could be cracked by a determined user.
>>
>>
>> Gord Dibben Excel MVP
>>
>> On Thu, 24 Mar 2005 11:15:02 -0800, SJC <SJC@discussions.microsoft.com> wrote:
>>
>> >I am using Excel 2000, so do you have any idea on how I can program this in?
>> >
>> >"Cesar Zapata" wrote:
>> >
>> >> If you are using excel 2003 and may be xp... when you protect you have
>> >> the option to allow sorting. If you have lesser versions then I think
>> >> you gonna have to go VBA.
>> >>
>> >>
>>
>>

[Gord Dibben]

Cesar

Just a point here..

You can only sort on areas that have been unlocked prior to protetimg the
worksheet.

2002 and 2003.


Gord Dibben Excel MVP

On 24 Mar 2005 10:53:50 -0800, "Cesar Zapata" <cesar.zapata@gmail.com> wrote:

>If you are using excel 2003 and may be xp... when you protect you have
>the option to allow sorting. If you have lesser versions then I think
>you gonna have to go VBA.