You could use the code near the end of http://www.gmayor.com/Userform_ComboBox.html to populate a userform list box This is the basic ADO coding to populate such a box (the code will populate either list or combo boxes, but for this type of task a list box is better).
If you want to see how it works in practice, see the Excel part of http://www.gmayor.com/Envelope_Label_Add_In.htm. (However that has the additional filtering shown below, to allow filtering by initial letter). You can select as many or as few addresses as you wish and you can insert them on envelopes, labels, letters etc. Somwhat similar (and includes the ability to add addresses to the worksheet without the need for users to manually access the worksheet) is http://www.gmayor.com/Address_Book.htm
The code including the filtering is:
Option Explicit
Private RS As Object
Private CN As Object
Private numrecs As Long, q As Long
Private strWidth As String
Private strFilter As String
Private strFilter2 As String
Private strFilter3 As String
Public Function xlFillList(ListOrComboBox As Object, _
iColumn As Long, _
strWorkbook As String, _
strRange As String, _
RangeIsWorksheet As Boolean, _
RangeIncludesHeaderRow As Boolean, _
ColumnTitle As String, _
Optional PromptText As String = "[Select Item]", _
Optional sLetterA As String, _
Optional sLetterB As String, _
Optional sLetterC As String)
If RangeIsWorksheet = True Then strRange = strRange & "$]"
Set CN = CreateObject("ADODB.Connection")
If RangeIncludesHeaderRow Then
CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strWorkbook & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
Else
CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strWorkbook & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=NO"";"
End If
Set RS = CreateObject("ADODB.Recordset")
RS.CursorLocation = 3
RS.Open "SELECT * FROM [" & strRange, CN, 2, 1 'read the data from the worksheet
If Not sLetterA = vbNullString And _
Not sLetterB = vbNullString And _
Not sLetterC = vbNullString Then
strFilter = "([" & ColumnTitle & "] LIKE '" & sLetterA & "*') OR ([" _
& ColumnTitle & "] LIKE '" & sLetterB & "*') OR ([" _
& ColumnTitle & "] LIKE '" & sLetterC & "*')"
ElseIf Not sLetterA = vbNullString And _
sLetterB = vbNullString And _
sLetterC = vbNullString Then
strFilter = "[" & ColumnTitle & "] LIKE '" & sLetterA & "*'"
Else
strFilter = ""
End If
If TypeName(ListOrComboBox) = "ComboBox" Then
If Not strFilter = vbNullString Then
If Not strFilter2 = vbNullString Then
strFilter = strFilter & "AND " & strFilter2
End If
If Not strFilter3 = vbNullString Then
strFilter = strFilter & "AND " & strFilter3
End If
End If
End If
If TypeName(ListOrComboBox) = "ListBox" Then
If strFilter = vbNullString Then
If Not strFilter2 = vbNullString Then
strFilter = strFilter & "AND " & strFilter2
End If
If Not strFilter3 = vbNullString Then
strFilter = strFilter & "AND " & strFilter3
End If
End If
End If
With RS
.MoveLast
numrecs = .RecordCount
.MoveFirst
If Not strFilter = vbNullString Then
On Error Resume Next
.Filter = strFilter
End If
End With
With ListOrComboBox
.ColumnCount = RS.Fields.Count
.Column = RS.GetRows(numrecs)
strWidth = vbNullString
For q = 1 To .ColumnCount
If q = iColumn Then
If strWidth = vbNullString Then
strWidth = .Width - 4 & " pt"
Else
strWidth = strWidth & .Width - 4 & " pt"
End If
Else
strWidth = strWidth & "0 pt"
End If
If q < .ColumnCount Then
strWidth = strWidth & ";"
End If
Next q
.ColumnWidths = strWidth
If TypeName(ListOrComboBox) = "ComboBox" Then
.AddItem PromptText, 0
If Not iColumn - 1 = 0 Then .Column(iColumn - 1, 0) = PromptText
.ListIndex = 0
End If
End With
'Cleanup
If RS.State = 1 Then RS.Close
Set RS = Nothing
If CN.State = 1 Then CN.Close
Set CN = Nothing
lbl_Exit:
Exit Function
End Function
If you want to roll your own, and require more information on how to address the filtering, let me know.