populate Excel dropdown with Access data

Here is a sample of a routine that can be called on the open of the workbook, or from a refresh button or whatever.
It queries an access database and populates the list box. An alternate apporach would be to create a MSQuery link to the db on another sheet and just use that as the ListRange value in the properties window. This sample uses a 2 column combobox, where the first column is the ID value and is not displayed. You would set the properties accordingly (I have ColumnCount = 2 and ColumnWidths = 0 pt;10 pt and my LinkedCell = Sheet1!A1)

Sub usbGetListItems()
    Dim dbe As DAO.DBEngine
    Dim ws As DAO.Workspace
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim s As msforms.ComboBox
    Dim i As Integer
    Dim lstcount As Long
    Dim varItem As Variant
    Dim objControl As Object

    Set dbe = New DAO.DBEngine
    Set ws = dbe.Workspaces(0)
    Set db = ws.OpenDatabase("D:\data\access2k\General2k.mdb")
    Set rs = db.OpenRecordset("SELECT ID, Color FROM tblColors")
   
    If rs.EOF = True And rs.BOF = True Then
        Err.Raise 55000, "usbGetListItems", "No records were returned!"
    Else
       
        rs.MoveFirst
        ' This must be a combobox added from the "Control Toolbox" toolbar,
        ' not the one from the "Forms" Toolbar
        Set objControl = Sheets("Sheet1").Shapes("ComboBox1").OLEFormat.Object
        Set s = objControl.Object
        lstcount = s.ListCount
        'remove all the items
        Do Until lstcount = 0
            s.RemoveItem 0
            lstcount = lstcount - 1
        Loop
        'add new items
        Do While Not rs.EOF
            s.AddItem
            s.Column(0, i) = rs.Fields("ID").Value
            s.Column(1, i) = rs.Fields("Color").Value
           
            rs.MoveNext
            i = i + 1
        Loop
    End If
Exit_usbGetListItems:
    On Error Resume Next
    If Not rs Is Nothing Then rs.Close: Set rs = Nothing
    If Not db Is Nothing Then db.Close: Set db = Nothing
    If Not ws Is Nothing Then ws.Close: Set ws = Nothing
    If Not dbe Is Nothing Then Set dbe = Nothing
    If Not s Is Nothing Then Set s = Nothing
    If Not objControl Is Nothing Then Set objControl = Nothing
Err_usbGetListItems:
    MsgBox "Error [" & Err.Number & "] " & Err.Source & vbCrLf & Err.Description, vbCritical, "usbGetListItems"
End Sub

Sorry, here is the correct version

Sub usbGetListItems()
    Dim dbe As DAO.DBEngine
    Dim ws As DAO.Workspace
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim s As msforms.ComboBox
    Dim i As Integer
    Dim lstcount As Long
    Dim varItem As Variant
    Dim objControl As Object

    On Error GoTo Err_usbGetListItems
   
    Set dbe = New DAO.DBEngine
    Set ws = dbe.Workspaces(0)
    Set db = ws.OpenDatabase("D:\data\access2k\General2k.mdb")
    Set rs = db.OpenRecordset("SELECT ID, Color FROM tblColors")
   
    If rs.EOF = True And rs.BOF = True Then
        Err.Raise 55000, "usbGetListItems", "No records were returned!"
    Else
       
        rs.MoveFirst
        ' This must be a combobox added from the "Control Toolbox" toolbar,
        ' not the one from the "Forms" Toolbar
        Set objControl = Sheets("Sheet1").Shapes("ComboBox1").OLEFormat.Object
        Set s = objControl.Object
        lstcount = s.ListCount
        'remove all the items
        Do Until lstcount = 0
            s.RemoveItem 0
            lstcount = lstcount - 1
        Loop
        'add new items
        Do While Not rs.EOF
            s.AddItem
            s.Column(0, i) = rs.Fields("ID").Value
            s.Column(1, i) = rs.Fields("Color").Value
           
            rs.MoveNext
            i = i + 1
        Loop
    End If
Exit_usbGetListItems:
    On Error Resume Next
    If Not rs Is Nothing Then rs.Close: Set rs = Nothing
    If Not db Is Nothing Then db.Close: Set db = Nothing
    If Not ws Is Nothing Then ws.Close: Set ws = Nothing
    If Not dbe Is Nothing Then Set dbe = Nothing
    If Not s Is Nothing Then Set s = Nothing
    If Not objControl Is Nothing Then Set objControl = Nothing
    Exit Sub
Err_usbGetListItems:
    MsgBox "Error [" & Err.Number & "] " & Err.Source & vbCrLf & Err.Description, vbCritical, "usbGetListItems"
End Sub

blakeh1,

I need to apply this dropdown to all cells in a column, so that when the user chooses any cell in the column, the dropdown may be chosen.

Thank you Krolfes

Sébastien

I just posted another question...can you help...

Hello, I have this problem in creating drop down lists based on External data reference included in other worksheets of the same file.
Data in the list is not refreshed as well as the data (query from Access) included in the other sheet.
Why..?