Problem with sorting data on a protected sheet..in (Excel97)

Topic

New Reply

I’m using Excel97
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

ps : theres no password on that proctected sheet just empty.i mean just hit enter or ok

Reply | Quote

Replies

I believe you will have to issue an unstruction to remove the sheet protection before sorting, even if the password is blank. I woudl try:

ActiveSheet.Unprotect password:=""

or just

ActiveSheet.Unprotect

I am not sure of the syntax; whenever I protect a sheet I use a pasword, so I have not had to use the ‘no password’ version

Reply | Quote

You can include the code to unprotect the worksheet befor you sort it, and reprotect when the sorting is done. Amend your macro to the following :

Sub Sorting()
    ActiveSheet.Unprotect
    Range("A1:A9").Select
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Just add the lines in red to your existing code and it should work.

Andrew C

Reply | Quote

i’m thinking like this..lol..

for Andrew solution..it just a thought okay..
let say the sheet is protected with password
let say “123”..so how can i use your code..

OR

i still hunt for this..
can you guys figure a way..i meant how
to sort data range on a protected sheet
because i have saw a guy do this..sort data
range on a protected sheet without unprotect
the sheet first.many thanks guy..hope you can
help me on this..appreciate it very much and
zillion thanks

Reply | Quote

Addition to my previous reply..

What I meant by using your code Andrew
is how can I manipulate your code if
the protected sheet contain password..
let say “123” your code that your provide
will prompt for password if the sheet
contains pasword.how can i overcome this
and the macro will unprotect it without
prompt for password and after finished, it
will protect again the sheet with “123”
password..hope you can understand my question
andrew..thanks again..

Reply | Quote

Put these two lines at the beginning of the Sub
Dim strPwd As String
strPwd = “123”
… and use
‘unprotect line:
ActiveSheet.Unprotect Password:=strPwd
‘
‘re-protect line:
ActiveSheet.Protect Password:=strPwd

Reply | Quote

Thanks..it works like a charm..

but still looking for the code to sort the data range without unprotect it first ..is it possible or is it just a dream..lol..thanks a bunch guys..

Reply | Quote

Hi,

If you use a macro to protect the sheet, you can use a setting called UserInterfaceOnly and set it to True. This will allow you to have the sort done by a macro, even though the sheet is protected.

Hope this is what you are looking for.

Reply | Quote

By the way, this also applies to using the filter on a protected sheet, but here you have to add the enableautofilter method of the activesheet object and set it to true:

ActiveSheet.EnableAutoFilter = True
ActiveSheet.Protect PassWord:=”MyPassWord”, DrawingObjects:=True, Contents:=True, Scenarios:=True, UserInterfaceonly:=True

Reply | Quote