So I have a sort problem. I have read many posts here and tried many
of the fixes to no avail so I write a new post.

The sort is kicked off by a label click. Here is the code I am using:

=======================
Called from here
=======================
Private Sub lblMainCost_Click()
SortMain ("S20")
Me.lblMainCost.SpecialEffect = fmSpecialEffectSunken
End Sub

=======================
Exectued here
=======================
Public Sub SortMain(SortColumnAndRow As String)

If Me.lstSeriesName.listIndex = -1 Then Exit Sub
Me.lstMain.listIndex = -1

TempList.Range("M20:W" & TempList.Cells(17, 24).Value - 1).Select
Selection.Sort Key1:=TempList.Range(SortColumnAndRow), _
Order1:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub
=======================

TempList is declared as a worksheet in the opening sub and resolves
properly.

I am getting the dreaded general 1004 error stating that the 'Sort
method of Range class failed'.

I have checked to make sure the range is not protected, not on a
hidden sheet and that the key is never blank. I have also removed the
'With' statements to reduce the 'dot' mistakes and removed unnessary
variables in favor of direct calls to cell values.

I have similar problems with another program being used the same way,
so I think its either the way its called or some of my syntax.

Any help here would be great!

It worked for me...

Is TempList a Public Variable declared at the top of a general module?
Do you Set TempList to an actual sheet?
Is TempList the active sheet?
Is there a numeric value in cells(17, 24)?
Does column(S) have sort values in it?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"theSquirrel"
wrote in message
So I have a sort problem. I have read many posts here and tried many
of the fixes to no avail so I write a new post.

The sort is kicked off by a label click. Here is the code I am using:

=======================
Called from here
=======================
Private Sub lblMainCost_Click()
SortMain ("S20")
Me.lblMainCost.SpecialEffect = fmSpecialEffectSunken
End Sub

=======================
Exectued here
=======================
Public Sub SortMain(SortColumnAndRow As String)

If Me.lstSeriesName.listIndex = -1 Then Exit Sub
Me.lstMain.listIndex = -1

TempList.Range("M20:W" & TempList.Cells(17, 24).Value - 1).Select
Selection.Sort Key1:=TempList.Range(SortColumnAndRow), _
Order1:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub
=======================

TempList is declared as a worksheet in the opening sub and resolves
properly.

I am getting the dreaded general 1004 error stating that the 'Sort
method of Range class failed'.

I have checked to make sure the range is not protected, not on a
hidden sheet and that the key is never blank. I have also removed the
'With' statements to reduce the 'dot' mistakes and removed unnessary
variables in favor of direct calls to cell values.

I have similar problems with another program being used the same way,
so I think its either the way its called or some of my syntax.

Any help here would be great!

TempList.Range("M20:W" & TempList.Cells(17, 24).Value - 1).Select

This looks funky.

Check out the address associated with this Selection ...

MsgBox Selection.Address

Better yet, save the address and MsgBox the address on error.

Here is a tip certain not to help you but I'll say it anyway to annoy
you.

Yet it's true.

If I'm doing a lot of sorting I'll put the data of each row into a
class, create a list of objects of that class, and then use a modified
version of JWalk's sort in place Sub.

Thank you for the reply's, answers to questions...

Jim Cone questions:
1. Is TempList a Public Variable declared at the top of a general
module?
2. Do you Set TempList to an actual sheet?
3. Is TempList the active sheet?
4. Is there a numeric value in cells(17, 24)?
5. Does column(S) have sort values in it?

1. Yes it is a public variable working in every other module and user
form I have in the program
2. I set it also in a general module to an actual sheet
3. TempList is the active sheet (which is why this puzzles me so)
4. Yes, 20 is the default value
5. Yes, if the program gets to that point there are values there
(That is the reason for the 'If Me.lstSeriesName.listIndex = -1 Then
Exit Sub' at the top of this sub.


Gimme_This_Gimme_That comments:
- I tried the msgbox Selection.Address and it turned up the correct
range in the case of my test '$M$20:$W$52'
- I know that it looks funny, but i did use a variable in place of
the direct reference in that place before and had the same problem.

I appreciate the any additional help. If you would like to see the
rest of the program, I can supply a link to it just email me. The
program is nearly done outside of this problem.

20 is the default value (in cells(17, 24)...

If selected range to sort is...
"TempList.Range("M20:W" & TempList.Cells(17, 24).Value - 1).Select"
then the sort range resolves to Range("M20:W19).
That's only two rows?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"theSquirrel"
wrote in message
Thank you for the reply's, answers to questions...

Jim Cone questions:
1. Is TempList a Public Variable declared at the top of a general
module?
2. Do you Set TempList to an actual sheet?
3. Is TempList the active sheet?
4. Is there a numeric value in cells(17, 24)?
5. Does column(S) have sort values in it?

1. Yes it is a public variable working in every other module and user
form I have in the program
2. I set it also in a general module to an actual sheet
3. TempList is the active sheet (which is why this puzzles me so)
4. Yes, 20 is the default value
5. Yes, if the program gets to that point there are values there
(That is the reason for the 'If Me.lstSeriesName.listIndex = -1 Then
Exit Sub' at the top of this sub.


Gimme_This_Gimme_That comments:
- I tried the msgbox Selection.Address and it turned up the correct
range in the case of my test '$M$20:$W$52'
- I know that it looks funny, but i did use a variable in place of
the direct reference in that place before and had the same problem.

I appreciate the any additional help. If you would like to see the
rest of the program, I can supply a link to it just email me. The
program is nearly done outside of this problem.

On Jun 14, 10:44 am, "Jim Cone" wrote:
20 is the default value (in cells(17, 24)...

If selected range to sort is...
"TempList.Range("M20:W" & TempList.Cells(17, 24).Value - 1).Select"
then the sort range resolves to Range("M20:W19).
That's only two rows?
--
Jim Cone
San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware

"theSquirrel"
wrote in message
Thank you for the reply's, answers to questions...

Jim Cone questions:
1. Is TempList a Public Variable declared at the top of a general
module?
2. Do you Set TempList to an actual sheet?
3. Is TempList the active sheet?
4. Is there a numeric value in cells(17, 24)?
5. Does column(S) have sort values in it?

1. Yes it is a public variable working in every other module and user
form I have in the program
2. I set it also in a general module to an actual sheet
3. TempList is the active sheet (which is why this puzzles me so)
4. Yes, 20 is the default value
5. Yes, if the program gets to that point there are values there
(That is the reason for the 'If Me.lstSeriesName.listIndex = -1 Then
Exit Sub' at the top of this sub.

Gimme_This_Gimme_That comments:
- I tried the msgbox Selection.Address and it turned up the correct
range in the case of my test '$M$20:$W$52'
- I know that it looks funny, but i did use a variable in place of
the direct reference in that place before and had the same problem.

I appreciate the any additional help. If you would like to see the
rest of the program, I can supply a link to it just email me. The
program is nearly done outside of this problem.

Problem solved!

As it turns out regardless of the cells being unlocked, the sort
function is unavailable when the sheet with the cells you are trying
to sort is locked.

I have not tried this on my other program but it works on this one.