Option Explicit
Private rs As Object
Private cn As Object
Private numrecs As Long, q As Long
Private strWidth As String

Public Function xlFillList(ListOrComboBox As Object, _
strWorkbook As String, _
strRange As String, _
bisRangeASheet As Boolean)
'A function to fill a list or combo box with data from an Excel worksheet or a named range in a worksheet
'ListOrComboBox is the name of the list or combo box
'strWorkbook is the name of the Excel data file
'strRange is the part of the data file to be used
'bisRangeASheet - is the range 'strRange' a sheet (true) or a named range (false)


Set cn = CreateObject("ADODB.Connection")

'Alternative connection 1
'*********************************
cn.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strWorkbook & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
'*********************************

'Alternative connection 2
'*********************************
'cn.Provider = "Microsoft OLE DB Provider for ODBC Drivers"
'cn.ConnectionString = "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & strWorkbook
'cn.Open
'*********************************

Set rs = CreateObject("ADODB.Recordset")
If bisRangeASheet = True Then
'strRange is a named worksheet use:
rs.Open "SELECT * FROM [" & strRange & "$]", cn, 2, 1 'read the data
Else
'strRange is a named range use:
rs.Open "SELECT * FROM [" & strRange & "]", cn, 2, 1 'read the data
End If

With rs
.MoveLast 'find the last record
numrecs = .RecordCount 'and note its number
.MoveFirst 'return to the start
End With

With ListOrComboBox 'load the records into the columns of the named list/combo box
.ColumnCount = rs.Fields.Count
.Column = rs.GetRows(numrecs)

'set the widths of the combo/list box columns (optional)
'the alternative is to display all the columns

'***********************
 strWidth = .Width - 2 & " pt;"
 For q = 2 To .ColumnCount
 strWidth = strWidth & "0 pt"
 If q < .ColumnCount Then
 strWidth = strWidth & ";"
 End If
 Next q
 .ColumnWidths = strWidth
'***********************
End With

'Cleanup
If rs.State = 1 Then rs.Close
If cn.State = 1 Then cn.Close
Set rs = Nothing
Set cn = Nothing
End Function

Sub CallUF()

Dim oFrm As UserForm1
Dim oVars As Word.Variables
Dim strTemp As String
Dim oRng As Word.Range
Dim i As Long
    
  Set oFrm = New UserForm1
  With oFrm
    .Show
  End With
    

End Sub

Private Sub UserForm_Initialize()

xlFillList UserForm1.ComboBox1, "E:\path_to_file\test.xlsx", "Sheet1", True

End Sub

Option Explicit
Private RS As Object
Private CN As Object
Private numrecs As Long, q As Long
Private strWidth As String
Public Function xlFillList(ListOrComboBox As Object, _
                           iColumn As Long, _
                           strWorkbook As String, _
                           strRange As String, _
                           RangeIsWorksheet As Boolean, _
                           RangeIncludesHeaderRow As Boolean, _
                           Optional PromptText As String = "[Select Item]")


    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

    With RS
        .MoveLast
        numrecs = .RecordCount
        .MoveFirst
    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

Private Sub UserForm_Initialize()
    xlFillList UserForm1.ComboBox1, 1, "E:\path_to_file\test.xlsx", "Sheet1", True, True
End Sub