building dropdown lists dynamically?

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

Answers


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

Need Your Help

How can I save or longly see linux kernel BUG message from console?

linux console kernel tty

I'm trying to develop device driver for the linux kernel, but I have a problem with debugging bug message.

protobuf-dt not working on Eclipse Luna

eclipse protocol-buffers xtext eclipse-luna

Installed protobuf-dt from the marketplace on Eclipse 4.4.2. When I attempt to open the editor or access the Protocol Buffers' preferences in settings I get the following error: