Hi,
this piece of code works well for me, it's in the initialize section
of the form with the drop down field on it. I got it from somewhere
else, but I'm not sure where.

Private Sub UserForm_Initialize()
Dim i As Integer
Dim aResults()

Dim cn As ADODB.Connection
Dim rsT As New ADODB.Recordset
Dim intTblCnt As Integer, intTblFlds As Integer
Dim strTbl As String
Dim rsC As ADODB.Recordset
Dim intColCnt As Integer, intColFlds As Integer
Dim strCol As String
Dim t As Integer, c As Integer, f As Integer
Set cn = New ADODB.Connection
With cn
   .Provider = "Microsoft.Jet.OLEDB.4.0"
   .ConnectionString = "Data Source=\\server\path
\filename.xls;Extended Properties=Excel 8.0;"
   .CursorLocation = adUseClient
   .Open
End With
rsT.Open "Select * from YourNamedRange", cn, adOpenStatic

i = 0

  With rsT
  ' This code populates the combo box with the values
  ' in the YourNamedRange named range in the .xls file. this example
table is 2 rows by 6 columns and is set as a named range.

     Do Until .EOF
        cboDiv.AddItem (i)
        cboDiv.Column(0, i) = rsT.Fields(0).Value
        cboDiv.Column(1, i) = rsT.Fields(1).Value
        .MoveNext
        i = i + 1
     Loop

  End With
End Sub

Hope that helps