Help with Combo Box code

[Ruan]

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

 

[Tom Ogilvy]

Remove the LinkedCell and Listfillrange properties and fill your combobox
with code, and return the results to a cell with code. So replace the
functionality of the Listfillrange and linkedcell properties with code.

 

[Ruan Walters]

Hello Tom,

I am still new at this, so I am not exactly sure what you mean by "fill
the combo box with code".

The LinkedCell property is empty and the ListFillRange property has the
name of the range of the List of Sort Criteria, which resides on another
sheet.

Ruan

 

[Tom Ogilvy]

Take that value in the listfillrange property and clear it.

instead

Private Sub worksheets_Activate()
combobox1.ListFillRange = ""
Combobox1.clear
for each cell in ["Sheet1!A1:A200"]
combobox1.AddItem cell.value
Next
End Sub

Right click on the sheet tab and select view code. Put in the above.

you have to activate the sheet to fill the combobox. So you don't want to
open to that sheet or you need to add similar code in the workbook open
event or have the workbook open event activate another sheet, then activate
this sheet.

 

[Ruan]

Tom,

I have cleared the ListFillRange property. I have added code to the
Workbook_Open() event to activate a different Sheet. I have added the below
code to the Sheet where the ComboBox control resides -

Private Sub worksheets_Activate()
cmbSort.ListFillRange = ""
cmbSort.Clear

For Each cell In ["shtComboLists!M5:M9"]
cmbSort.AddItem cell.Value
Next
End Sub


Do I still keep the below code -

Private Sub cmbSort_Change()
Sort_Data ActiveSheet, "1234", cmbSort.Value
End Sub


The ComboBox control is not being filled up when I activate the Sheet. Is
there something else I am doing wrong?

Ruan

Take that value in the listfillrange property and clear it.

instead

Private Sub worksheets_Activate()
combobox1.ListFillRange = ""
Combobox1.clear
for each cell in ["Sheet1!A1:A200"]
combobox1.AddItem cell.value
Next
End Sub

Right click on the sheet tab and select view code. Put in the above.

you have to activate the sheet to fill the combobox. So you don't want to
open to that sheet or you need to add similar code in the workbook open
event or have the workbook open event activate another sheet, then activate
this sheet.

--
Regards,
Tom Ogilvy

Hello Tom,

I am still new at this, so I am not exactly sure what you mean by "fill
the combo box with code".

The LinkedCell property is empty and the ListFillRange property has the
name of the range of the List of Sort Criteria, which resides on another
sheet.

Ruan