Run-time error 1004...

[JSnow]

I have a workbook with a macro that sorts rows alphabetically. I've now
shared the workbook and now my macro doesn't work. Here's the macro followed
by the error I'm getting:

Sub ALPHA()
ActiveSheet.Unprotect
Range("B3:H999").Sort Key1:=Range("B2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Run-time error ‘1004’: Unprotected method of Worksheet failed

I tried to ignore lines 2 and 6 thinking that the protection codes were the
problem. Now I get the following error:

Sub ALPHA()
' ActiveSheet.Unprotect
Range("B3:H999").Sort Key1:=Range("B2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
' ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

I get the following error:
Run-time error ‘1004’: Sort method of Range class failed

Please help me fix this. Thank you.

 

[Mike H]

Hi,

Both of these work fine for me.

Mike

 

[JSnow]

These don't work on a SHARED workbook. That's my problem.

 

[Mike H]

Apologies,

I missed the shared bit and it's the protection that's the issue and as far
as i'm aware there's no way around it. it's one of the restrictions on a
shered workbook

have a look here

http://www.exceladvisor.net/sub/6_shared.htm

Mike