building dropdown lists dynamically?

excel, excel-2007, vba

Solution

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

Problem

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

Original source