Hello,

I have an excel document with 6 sheets. Sheet2 has a Combo Box control which
I use to sort the data. I have the code in the Sheet2 code module.

Everything works great until I do a "Save As", then I sometimes get a
"Run-time error '1004': Sort Method of Range Class Failed" or for some
reason the Sheet5 becomes Active. I have no idea why this is happening.
Please Help!!

Here is the code for the Combo Box


Private Sub cmbSort_Change()

Sort_Data ActiveSheet, "1234", cmbSort.Value

End Sub


Private Sub Sort_Data(sht As Worksheet, pwd As String, val As String)

' Unprotect Worksheet
sht.Unprotect Password:=pwd

With Worksheets("PatientList")
If val = "Patient Name" Then
' Sort by Status by Patient Name
.Range("PatientData").Sort Key1:=.Range("Status"), _
Order1:=xlAscending, Key2:=.Range("Patient_Name"), _
Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

ElseIf val = "Patient Name (No Status)" Then
' Sort by Patient Name (No Status)
.Range("PatientData").Sort Key1:=.Range("Patient_Name"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Else
' Sort by Assigned Counselor Initials (No Status)
.Range("PatientData").Sort Key1:=.Range("Couns_Assigned"), _
Order1:=xlAscending, Key2:=.Range("Patient_Name"), _
Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End If
End With 'Worksheets("PatientList")

With sht
.Activate 'just to be SURE it is active
.Range("B9").Select

' Protect Worksheet
.Protect Password:=pwd, Scenarios:=True
End With
End Sub