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