building dropdown lists dynamically?

Question

I need to build a drop down list dynamically, whereby after entering particular text into a cell I then execute some SQL and build a Dropdown from the returned rows.

How is the event concentrated on the value of just one cell (rather than the whole spreadsheet) done?

Must I "paste" the SQL row values onto a spreadsheet before I create the Dropdown? Is it possible in VBA to populate the Dropdown without having to paste values onto a spreadsheet and then highlight them to create the Dropdown?

Thanks

Answer 1

No it is not necessary to paste values in the sheet to create the dropdown. See this example

Option Explicit

Sub Sample()
    Dim dvList As String

    '~~> You can construct this list from your database
    dvList = "Option1, Option2, Option3"

    '~~> Creates the list in Sheet1, A1
    With Sheets("Sheet1").Range("A1").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=dvList
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub