动态构建下拉列表？ [英] building dropdown lists dynamically?

问题描述

我需要动态地建立一个下拉列表，从而在将特定的文本输入到单元格之后，然后执行一些SQL，并从返回的行中构建一个下拉列表 p>

事件如何集中在一个单元格（而不是整个电子表格）的值上？

必须在创建下拉列表之前，将SQL行值粘贴到电子表格上？在VBA中可以填充 Dropdown ，而无需将值粘贴到电子表格上，然后突出显示它们以创建下拉列表？

谢谢

解决方案

没有必要粘贴工作表中的值创建下拉列表。看到这个例子

  Option Explicit 
 
 Sub Sample（）
 Dim dvList As String 
 
'~~>您可以从数据库中构建此列表
 dvList =Option1，Option2，Option3
 
'~~>创建Sheet1中的列表，A1 
带表格（Sheet1）。范围（A1）。验证
 .Delete 
 .Add类型：= xlValidateList，AlertStyle：= xlValidAlertStop，Operator ：= _ 
 xlBetween，Formula1：= dvList 
 .IgnoreBlank = True 
 .InCellDropdown = True 
 .InputTitle =
 .ErrorTitle =
 .InputMessage =
 .ErrorMessage =
 .ShowInput = True 
 .ShowError = True 
 End with 
 End Sub 
  

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

解决方案

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

这篇关于动态构建下拉列表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！