View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
theSquirrel theSquirrel is offline
external usenet poster
 
Posts: 27
Default Excel Sort Problem...

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!