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

 

[Bengt E]

I have encountered a similar protection problem. I have a macro that opens workbook a, selects a few lines in it, copies it to the copy-buffer, then opens workbook b and tries to paste the copied lines to a specific worksheet in workbook b.

In my first version of the solution, the target worksheet in workbook b was protected, so I unprotected it, pasted my information and protected it again. This worked and worked not, in a very exact pattern: worked - didn't work - worked - didn't work. A very mysterious behaviour. However, in version 2, I removed the code for the protection in workbook b (i.e. I left it unprotected all the time), and all of a sudden, the code worked fine, every time!

It turned out, that when the code failed, there was nothing in the paste buffer to paste, although there had been information in it right after the Selection.Copy line.

Are there some special considerations that you have to have in mind when protecting a worksheet.