Solved: Populate Drop Down Box with SQL Query

[mferrisi]

I created a Drop Down Box from Forms and need to populate it with results from an SQL Query. I can't seem to figure out how to do it.

Thanks for you for your suggestions!

[Kenneth Hobs]

The process depends on what type of SQL but here is one method that I used for a userform's listbox control.

Private Sub UserForm_Initialize()
' fill ListBox1 with data from a closed workbook
' can also be used from other applications to read data from an open workbook
Dim tArray As Variant, sListFile1 As String, sListFile2 As String
'sListFile1 = "F:\Excel\RangeNameLists.xls"
'sListFile2 = "E:\Excel\Listbox\RangeNameLists.xls"
sListFile1 = ActiveWorkbook.Path & "\" & "RangeNameLists.xls"
'MsgBox sListFile1, , Dir(sListFile1)
If Dir(sListFile1) <> "" Then
tArray = ReadDataFromWorkbook(sListFile1, "Fruits2")
ElseIf Dir(sListFile2) <> "" Then
tArray = ReadDataFromWorkbook(sListFile2, "Fruits")
End If
'MsgBox UBound(tArray, 1), , UBound(tArray, 2)
FillListBox Me.ListBox1, tArray
Erase tArray
End Sub

'In a module:
Rem https://www.exceltip.com/st/Fill_a_ListBox-control_with_values_from_another_workbook_using_VBA_in_Microsoft_Excel/410.html
Function ReadDataFromWorkbook(SourceFile As String, _
SourceRange As String) As Variant
' requires a reference to the Microsoft ActiveX Data Objects library
' (menu Tools, References in the VBE)
' if SourceRange is a range reference:
' this function can only return data from the first worksheet in SourceFile
' if SourceRange is a defined name reference:
' this function can return data from any worksheet in SourceFile
' SourceRange must include the range headers
' examples:
' varRecordSetData = _
ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "A1:A21")
' varRecordSetData = _
ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "A1:B21")
' varRecordSetData = _
ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "DefinedRangeName")
Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset
Dim dbConnectionString As String
dbConnectionString = _
"DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=1;DBQ=" & SourceFile
Set dbConnection = New ADODB.Connection
On Error GoTo InvalidInput
dbConnection.Open dbConnectionString ' open the database connection
Set rs = dbConnection.Execute("[" & SourceRange & "]")
On Error GoTo 0
ReadDataFromWorkbook = rs.GetRows
' returns a two dim array with all records in rs
rs.Close
dbConnection.Close ' close the database connection

Set rs = Nothing
Set dbConnection = Nothing
On Error GoTo 0
Exit Function
InvalidInput:
MsgBox "The source file or source range is invalid!", _
vbExclamation, "Get data from closed workbook"
Set rs = Nothing
Set dbConnection = Nothing
End Function

Sub FillListBox(lb As MSForms.ListBox, RecordSetArray As Variant)
' fills lb with data from RecordSetArray
Dim r As Long, c As Long
With lb
.Clear
For r = LBound(RecordSetArray, 2) To UBound(RecordSetArray, 2)
.AddItem
For c = LBound(RecordSetArray, 1) To UBound(RecordSetArray, 1)
.List(r, c) = RecordSetArray(c, r)
Next c
Next r
.ListIndex = -1 ' no item selected
End With
End Sub

[mferrisi]

Sheets(1).Shapes(1).Name

This returns "Drop Down 1"

Does this mean that it is an MSForms.[Something]? My options for MSForms include ComboBox and ListBox, but is the Drop Down something else?

[mferrisi]

It appears to be an xlDropDown.

Sheets(1).Shapes("Drop Down 10").ControlFormat.AddItem "ppp", 2