Combo Box

[Kerry]

Hi

I have created a combo box (cboContactList)on a user form
which lists my contacts in outlook. However, the list is
sorted in the order in which the contacts were created
rather than in ascending alpha order - how can I change
this please.

Many thanks in advance.

Please find my user form initialize code below:

Private Sub UserForm_Initialize()
Dim oApp As Outlook.Application
Dim oNspc As NameSpace
Dim oItm As ContactItem
Dim x As Integer
Dim y As Integer
If Not DisplayStatusBar Then
DisplayStatusBar = True
End If
StatusBar = "Please Wait....."
sender:
x = 0
Set oApp = CreateObject("Outlook.Application")
Set oNspc = oApp.GetNamespace("MAPI")
For Each oItm In oNspc.GetDefaultFolder
(olFolderContacts).Items
With Me.cboContactList
.AddItem (oItm.FullName)
.Column(1, x) = oItm.JobTitle
.Column(2, x) = oItm.CompanyName
.Column(3, x) = oItm.BusinessAddress
.Column(4, x) = oItm.BusinessTelephoneNumber
.Column(5, x) = oItm.BusinessFaxNumber

End With
x = x + 1
Next oItm
StatusBar = ""
Set oItm = Nothing
Set oNspc = Nothing
Set oApp = Nothing
recipient:
y = 0
Set oApp = CreateObject("Outlook.Application")
Set oNspc = oApp.GetNamespace("MAPI")
For Each oItm In oNspc.GetDefaultFolder
(olFolderContacts).Items
With Me.cboContactList2
.AddItem (oItm.FullName)
.Column(1, y) = oItm.JobTitle
.Column(2, y) = oItm.CompanyName
.Column(3, y) = oItm.BusinessAddress
.Column(4, y) = oItm.BusinessTelephoneNumber
.Column(5, y) = oItm.BusinessFaxNumber

End With
y = y + 1
Next oItm
StatusBar = ""
Set oItm = Nothing
Set oNspc = Nothing
Set oApp = Nothing
End Sub

 

[Peter Hewett]

Hi Kerry

There's two solutions to this problem. You can either add them to the ComboBox in order
or you can create an intermediate array and sort the data before building the combo box.
Given that you're using a multi column ComboBox control I'd do it the first way. The
following algorithm is crude (but effective) in that it does not do a binary search of the
ComboBox (which is both feasible and practical - if you want to take the time or you have
an enormous number of Outlook contacts).

Private Sub AddToComboBox(ParamArray avarValues() As Variant)
Dim lngIndex As Long
Dim lngColumns As Long
Dim strValue As String


' The first supplied value determines which row we insert before/after
strValue = avarValues(0)

' Crude method to add values to the listbox alphabetically (ascending order)
With cboContactList
For lngIndex = 0 To .ListCount - 1

If .List(lngIndex) >= strValue Then
Exit For
End If
Next

' The Parameter array contains 1 value per column
.AddItem strValue, lngIndex
For lngColumns = 1 To UBound(avarValues)
.List(lngIndex, lngColumns) = avarValues(lngColumns)
Next
End With
End Sub ' AddToComboBox


Call the above code like this:

With oItm
AddToComboBox JobTitle, CompanyName, BusinessAddress, _
BusinessTelephoneNumber, BusinessFaxNumber
End With

HTH + Cheers - Peter