sort combobox

This earlier post has code that will fill a combo box with a sorted list:
http://www.mrexcel.com/board2/viewtopic.php?p=1162304#1162304

Thanks Datsmart

I guess I will use

but I have to fill Coll variable with the names of files... do you know how to gather the names of the files (in the same folder with excel file) and put them in the collection? What I am trying to do is, after I select the file name from the combobox I will click on a button and it will open the file...


thanks.

A quick forum search gave me this link:
http://www.mrexcel.com/board2/viewtopic.php?t=253594

Hi Datsmart,

Your code works great. I changed it a little for my needs (see below). When user click Confirm Button, I need to refer back to the Workbooks("misdfcustomer.xls").Worksheets("custinfo") and extract data that corresponds to the selection made in the combobox. But since the order in the combobox has been re-arranged, how can i correctly reference to the Worksheet?

Private Sub UserForm_Initialize()

Dim k As Long
Dim j As Long
Dim Temp As Variant

OpenNotShow ("misdfcustomer.xls")

ComboBoxCustComp.Clear

'Extract list from worksheet and populate combobox
TotalRow = Workbooks("misdfcustomer.xls").Worksheets("custinfo").Range("A65536").End(xlUp).Row
For i = 2 To TotalRow
ComboBoxCustComp.AddItem Workbooks("misdfcustomer.xls").Worksheets("custinfo").Cells(i, 3).Value
Next i

With ComboBoxCustComp
For k = 0 To .ListCount - 1
For j = k + 1 To .ListCount - 1
If .List(k) > .List(j) Then
Temp = .List(j)
.List(j) = .List(k)
.List(k) = Temp
End If
Next j
Next k
End With

'Use drop-down list
ComboBoxCustComp.Style = fmStyleDropDownList
'Combo box values are ListIndex values
ComboBoxCustComp.BoundColumn = 0
'Set combo box to first entry
ComboBoxCustComp.ListIndex = 0

Workbooks("misdfcustomer.xls").Close SaveChanges:=False

End Sub




Private Sub CBConfirm_Click()

'Extract Data Corresponding To Combo Selection

OpenNotShow ("misdfcustomer.xls")

Workbooks(datafile).Worksheets(refsheet).Cells(NewRow, 3).Value = Workbooks("misdfcustomer.xls").Worksheets("custinfo").Cells(ComboBoxCustComp.Value + 2, 3).Value

Workbooks("misdfcustomer.xls").Close SaveChanges:=False

End Sub