Option Explicit
Public Function xlFillList(oListOrComboBox As Object, strWorkbook As String, _
                           bSuppressHeader As Boolean, strSQL As String, _
                           bSingleColumn As Boolean)
Dim oConn As Object
Dim oRecordSet As Object
Dim lngNumRecs As Long, lngIndex As Long
Dim strWidth As String
Dim strConnection As String

  
  
  'Create connection:
  Set oConn = CreateObject("ADODB.Connection")
  If bSuppressHeader Then
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                    "Data Source=" & strWorkbook & ";" & _
                    "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
  Else
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                    "Data Source=" & strWorkbook & ";" & _
                    "Extended Properties=""Excel 12.0 Xml;HDR=NO"";"
  End If
  oConn.Open ConnectionString:=strConnection
  Set oRecordSet = CreateObject("ADODB.Recordset")
  'Read the data from the worksheet.
  oRecordSet.Open strSQL, oConn, 3, 1 '3: adOpenStatic, 1: adLockReadOnly
  With oRecordSet
    'Find the last record.
    .MoveLast
    'Get count.
    lngNumRecs = .RecordCount
    'Return to the start.
    .MoveFirst
  End With
  With oListOrComboBox
    .Clear
    'Load the records into the columns of the named list/combo box.
    .ColumnCount = oRecordSet.Fields.Count
    .Column = oRecordSet.GetRows(lngNumRecs)
    strWidth = vbNullString
    If bSingleColumn Then
     'Set the widths of the combo/list box columns to display only the first column.
      strWidth = .Width - 20 & " pt;"
      For lngIndex = 2 To .ColumnCount
        strWidth = strWidth & "0 pt"
        If lngIndex < .ColumnCount Then
          strWidth = strWidth & ";"
        End If
      Next lngIndex
    Else
      For lngIndex = 1 To .ColumnCount
        strWidth = strWidth & Val(.Width \ .ColumnCount) - 10 & " pt;"
      Next lngIndex
      .ColumnWidths = strWidth
    End If
  End With
Cleanup:
  If oRecordSet.State = 1 Then oRecordSet.Close
  Set oRecordSet = Nothing
  If oConn.State = 1 Then oConn.Close
  Set oConn = Nothing
lbl_Exit:
  Exit Function
End Function

Private Sub UserForm_Initialize()
Dim DataSourcePath As String
Dim strSQL As String
  DataSourcePath = ThisDocument.Path & "\Basic Fill.xlsx"
  'Get all data from sheet named "BasicI", exclude heading row, single Column
  strSQL = "SELECT * FROM [BasicI$];"
  mod_ExcelInteropSA.xlFillList lstBasicI, DataSourcePath, "True", strSQL, "True"
  'Get all data from sheet named "BasicII", including heading row, show all columns
  strSQL = "SELECT * FROM [BasicII$];"
  mod_ExcelInteropSA.xlFillList lstBasicII, DataSourcePath, "False", strSQL, "False"
  'Get data from columns headed "Name" and "Amount" from sheet named "BasicIII", exclude heading row, show all columns
  strSQL = "SELECT Name, Amount From [BasicIII$];"
  mod_ExcelInteropSA.xlFillList lstBasicIII, DataSourcePath, "True", strSQL, "False"
lbl_Exit:
  Exit Sub
End Sub