Help with a Combo Box Change Event

[Ruan]

Hello,

I only receive the follow error when I do a "Save As" for my excel document.

Run-time error '1004':
Select Method of Range Class Failed


Why do I get this error when I use "Save As" and the debugger highlights the
following line in the below code?
Range("B9").Select

Is there a way to use the sheet name instead of "ActiveSheet"?



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 sht
If val = "Patient Name" Then
' Sort by Status by Patient Name
With Worksheets("PatientList")
.Range("PatientData").Sort Key1:=.Range("Status"), _
Order1:=xlAscending, Key2:=.Range("Patient_Name"), _
Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End With
Range("B9").Select

ElseIf val = "Discharge Date (No Status)" Then
' Sort by Discharge Date (No Status)
With Worksheets("PatientList")
.Range("PatientData").Sort Key1:=.Range("Discharge_Date"), _
Order1:=xlAscending, Key2:=.Range("Patient_Name"), _
Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End With
Range("B9").Select

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

End If
End With

' Protect Worksheet
sht.Protect Password:=pwd, Scenarios:=True

End Sub


Thanks
Ruan

 

[Paul Robinson]

Hi Ruan,
You are missing a . before your Range("B9").
Try specifying which sheet Range("B9") is on. If something is going to
happen to other sheets (like sorting it) you should explicitly
activate before selecting. That way, you don't have to worry about
whether some worksheet method is going to activate a sheet or not.
e.g. (Tidied your code a bit too. Nesting With..end With statements
always makes me feel a bit quesy)


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 = "Discharge Date (No Status)" Then
' Sort by Discharge Date (No Status)
.Range("PatientData").Sort
Key1:=.Range("Discharge_Date"), _
Order1:=xlAscending,
Key2:=.Range("Patient_Name"), _
Order2:=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

regards
Paul