Sort method of range

[Richard Clarke]

Hi

I am performing a sort programatically in Excel 2002 on Windows XP

The line of code to sort is as follows:

Range(Cells(15, 1), Cells(endrow,
Range("iNonQualStatus").Column)).Sort Key1:=Range("iItemType"),
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=ordercustomnumber, MatchCase:=False,
Orientation:=xlTopToBottom

This throws the following error:

Sort Method of Range class failed

The sheet is protected both programatically and using Excel's protect
option from the menus.

No matter what I try regarding setting different protections at
run-time or saving the sheet with different protections, whenever I
hit the line of code above, the allowsorting propery is false.

In order to test sorting I created a new workbook and sheet to test
the sorting functionality and even this fails - with no protections,
and allowsorting reporting true when interrogated at runtime (although
this time the error is 1004 Application-defined or object-defined
error).

Can anyone suggest why this doesn't work in either scenario?

Many thanks

 

[Tom Ogilvy]

You didn't say, but have you gotten the code to work with the sheet
unprotected. If not, get it to work with the sheet unprotected, then attack
the protection problem.

Regards,
Tom Ogilvy

 

[andres a]

Rich
it looks to me that you are missing something on the first
line of your code.

Range(cells(15,1), Cells(endrow,???????
where is the end of the cells
if it is the next line you are missing the space and the
udnerscore.

Check the protection.
I do not belive you can do sorts when cell are protected.

Andres

 

[Richard Clarke]

You didn't say, but have you gotten the code to work with the sheet
unprotected. If not, get it to work with the sheet unprotected, then attack
the protection problem.

Tom

I cannot get it to work even when all the protections on all the
sheets are turned off at design time and all protection code removed.
I created a test sheet with similar functionality to the sort part and
it worked.

I cannot set the takefocusonclick property as it is not a command
button but a custom command bar button being clicked to call the
subroutine.

Doing sheet.unprotect and then sheet.activate has not worked either. I
am pulling out my hair with this one now.

Desperate

 

[Richard Clarke]

You didn't say, but have you gotten the code to work with the sheet
unprotected. If not, get it to work with the sheet unprotected, then attack
the protection problem.

Tom

I cannot get it to work even when all the protections on all the
sheets are turned off at design time and all protection code removed.
I created a test sheet with similar functionality to the sort part and
it worked.

I cannot set the takefocusonclick property as it is not a command
button but a custom command bar button being clicked to call the
subroutine.

Doing sheet.unprotect and then sheet.activate has not worked either. I
am pulling out my hair with this one now.

Desperate

 

[Richard Clarke]

do you have a range named iNonQualStatus? If not, your code is looking for
a range with that name

Assuming iNonQualStatus is a variable of type integer (begins with i), then
Range(integer) will return an error.

Columns(integer).Column would work

Dim iNonQualStatus as Integer
iNonQualStatus = 23

Range(Cells(15, 1), Cells(endrow,Columns(iNonQualStatus).Column)).

Tom

The range exists on the sheet, this code all works fine on Windows NT4
with Excel 97.

At first I thought it was to do with the protections, but normally
excel lets you know the cells are protected and cannot be changed if
this is the case. No matter what I try from trawling the groups,
nothing works.

Regards

 

[Richard Clarke]

You didn't say, but have you gotten the code to work with the sheet

unprotected. If not, get it to work with the sheet unprotected, then attack
the protection problem.

Tom

This doesn't work even when I remove all the sheets' protections, in
the designer and at runtime, and I also removed all the cell locking.

Do you have any idea as to what could possibly prevent the
allowsorting value to always be false even when protection is
disabled?

Many thanks

 

[Richard Clarke]

do you have a range named iNonQualStatus? If not, your code is looking for
a range with that name

Tom

The range does exist, and also, the code works perfectly on NT with
Excel 97. Something has changed in the XP version (2002, version 10,
whatever)

Any ideas?

Many thanks