Hello,

I'm trying to program in MSExcel 2003 a sort in a workbook that I
intend to lock down to prevent users from affecting the list box
validation source and the code keeps breaking at this line:
SrcRng.Sort Header:=xlYes, DataOption1:=xlSortNormal, Key1:=SKey1,
Order1:=Dir1

I can't figure this one out. Here is the full script:
Sub KCarSort()
Dim SKey1 As Range 'Sort Key 1
Dim SKey2 As Range 'Sort Key 2
Dim SKey3 As Range 'Sort Key 3
Dim Dir1 As String 'Sort Direction 1
Dim Dir2 As String 'Sort Direction 2
Dim Dir3 As String 'Sort Direction 3

Dim SrcRng As Range
Dim SrcHdrRng As Range

With ActiveWorkbook
MsgBox (Range(.Names("skey1")).Value)
Set SrcHdrRng = Range("A1:AL1")
Set SrcRng = Range("A1:AL1001")
If Range(.Names("Skey1")).Value < "" Then
Set SKey1 = SrcHdrRng.Find(what:=Range(.Names("Skey1")).Value)
If Range(.Names("Dir1")).Value < "" Then
Dir1 = "xl" & Range(.Names("dir1")).Value
Else
Dir1 = "xlAscending"
End If
Else: GoTo ENDING
End If

If Range(.Names("Skey2")).Value < "" Then
Set SKey2 = SrcHdrRng.Find(what:=Range(.Names("Skey2")).Value)
If Range(.Names("Dir2")).Value < "" Then
Dir2 = "xl" & Range(.Names("dir2")).Value
Else
Dir2 = "xlAscending"
End If
End If

If Range(.Names("Skey3")).Value < "" Then
Set SKey3 = SrcHdrRng.Find(what:=Range(.Names("Skey3")).Value)
If Range(.Names("Dir3")).Value < "" Then
Dir3 = "xl" & Range(.Names("dir3")).Value
Else
Dir3 = "xlAscending"
End If
End If


If Not SKey2 Is Nothing Then
If Not SKey3 Is Nothing Then
SrcRng.Sort Key1:=SKey1.Address, Order1:=Dir1 _
, Key2:=SKey2.Address, Order2:=Dir2 _
, Key3:=SKey3.Address, Order3:=Dir3
Else
SrcRng.Sort Key1:=SKey1.Address, Order1:=Dir1 _
, Key2:=SKey2.Address, Order2:=Dir2
End If
Else
SrcRng.Sort Header:=xlYes, DataOption1:=xlSortNormal, Key1:=SKey1,
Order1:=Dir1
End If
End With
ENDING: MsgBox ("You must select at least 1 criteria in the Sort 1
dropdown to perform a sort")
End Sub


Your assistance is greatly appreciated.


Steven