MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Problem with sorting data on a protected sheet..inside..


Posted by NiuB on July 07, 2001 11:29 AM

I try to sort a data range in a protected sheet..but
everytime it gives me an error..the error is :

Run-time error '1004':

Sort method of Range class failed

It will shade the range that I'm trying to sort..

How do I overcome this problem coz its very annoying..
Many thanks


Posted by Scott S on July 07, 2001 11:48 AM

You could try to Unprotect the sheet in your code before sorting, and then sort, and protect your sheet again. Here is an example:

Sub Sort_Protect()

ActiveSheet.Unprotect 'Unprotects Sheet
Range("A1:E100").Select 'Selects Range to be sorted

Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom ' Sorts ascending from Column A

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True ' Protects Sheet

Range("A1").Select 'Ends with selecting top cell.
End Sub

Posted by NiuB on July 07, 2001 11:56 AM

is there any method to sort it while it protected
coz i saw a guy do this and still succeed..any ideas..many thanks
again..

Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom ' Sorts ascending from Column A ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True ' Protects Sheet

Posted by Ivan F Moala on July 07, 2001 5:56 PM

Yes there is - only draw back is you will
have to reactivate the protection scheme upon
opening...............just use

ActiveSheet.Protect "test", True, True, True, True
In your code ....suggest you put it into your
event code for openning.........
The key is the last True......have a look @
userinterfaceonly for Protection.

Ivan

is there any method to sort it while it protected

Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom ' Sorts ascending from Column A ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True ' Protects Sheet

Posted by NiuB on July 07, 2001 6:25 PM

Sorry..but i'm just a novice Ivan.would you mind
write down the code..please and thanks

Yes there is - only draw back is you will

Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom ' Sorts ascending from Column A ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True ' Protects Sheet

Posted by Scott S on July 07, 2001 10:15 PM

While in excel, hold down the "alt" key and press "F11". This will open your Visial Basic Editor. In the project window, select "ThisWorkbook". On the Main window, select "Workbook" from the left pull down, and "Open" from the right pull down. Go ahead and paste this code:

ActiveSheet.Protect "test", True, True, True, True

between "Private Sub Workbook_Open()" and "End Sub" You can now close the VB Editor. Go ahead and save your file. Now, when your workbook opens, it will be protected, but the Sort macro should still work (I believe sort will only work if it is set up as a macro).

By the way, in the line of code that you are pasting in, the "test" part is the password. You can change this to any password you desire.

Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom ' Sorts ascending from Column A ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True ' Protects Sheet

Posted by Ivan F Moala on July 07, 2001 11:30 PM

Thanks Scott.....

Thanks Scott......

Ivan

Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom ' Sorts ascending from Column A ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True ' Protects Sheet