Following on from the tutorials you listed, I think a User Form will suffice.
I have tried to follow the instructions here:
http://www.gmayor.com/Userform_ComboBox.html
However I cannot get it to work. There are no errors, but the ComboBox remains empty - it isn't populating it.
The code is exactly as in the tutorial
Code:
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
This is called when the UserForm is initialised.
Code:
Private Sub UserForm_Initialize()
xlFillList UserForm1.ComboBox1, "E:\path_to_file\test.xlsx", "Sheet1", True
End Sub
If the filename is incorrect then an error occurs, so I know it is checking for the data, it's just not putting it in the ComboBox...
Can anyone guide me here?
Thanks in advance.
Mark