Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort method of range
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 -- Rich (change nospam to mlu035 to reply direct) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort method of range
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 "Richard Clarke" wrote in message om... 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 -- Rich (change nospam to mlu035 to reply direct) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort method of range
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 -----Original Message----- 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 -- Rich (change nospam to mlu035 to reply direct) . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort method of range
"Tom Ogilvy" wrote in message ...
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 -- Rich (change nospam to mlu035 to reply direct) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort method of range
"Tom Ogilvy" wrote in message ...
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 -- Rich (change nospam to mlu035 to reply direct) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort method of range
"Tom Ogilvy" wrote in message ...
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 -- Rich (change nospam to mlu035 to reply direct) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort method of range
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort method of range
"Tom Ogilvy" wrote in message ...
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 -- Rich |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need really fast method to sort data for my part numbering spread | Excel Discussion (Misc queries) | |||
Set range with Find method | Excel Discussion (Misc queries) | |||
Autofill method of range class failed | Excel Discussion (Misc queries) | |||
VBA Sort method w/more than 3 Keys | Excel Discussion (Misc queries) | |||
ClearContents method on a passed range | New Users to Excel |