"Dkso" wrote ...

172,000 records on
this table (and obviously only 65,532 will fit onto an Excel spreadsheet).
I have used a ComboBox in MS Access to achieve what I want to do but don't
know how to do it in Excel.
I want to click on the ComboBox or start typing an item code and the item be
displayed in the dropdown box.

This in a userform with a combo named ComboBox1 (check the connection
string for northwind):

Option Explicit

Private Sub UserForm_Activate()

Dim Con As Object
Dim rs As Object
Dim strConJet As String
Dim strSql1 As String

' Amend the following constants to suit
Const CONN_STRING_JET As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\" & _
"Microsoft Visual Studio\VB98\NWIND.mdb"

strConJet = CONN_STRING_JET

' Build sql statement
strSql1 = "SELECT LastName FROM Employees"

' Open connection
Set Con = CreateObject("ADODB.Connection")
With Con
.Open strConJet
Set rs = .Execute(strSql1)

' Write data to combo
ComboBox1.List = _
Excel.Application.Transpose(rs.GetRows)

.Close
End With

End Sub

Jamie.

--