如何从 Excel (VBA) 中的代码设置下拉选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18967436/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to set dropdown selection from code in Excel (VBA)
提问by user2808401
I am trying to make a filter for an Excel table. I have 2 dropdown boxes with the items lists dynamically added in a macro that's called when the table is refreshed.
我正在尝试为 Excel 表格制作过滤器。我有 2 个下拉框，其中的项目列表动态添加到一个宏中，该宏在刷新表时调用。
Set selectBook = Worksheets("Report").DropDowns("DropDownBook")
selectBook.RemoveAllItems
For Each b In books
selectBook.AddItem (b)
Next
Where "books" is an array with the new values. Code for the other dropdown with "authors" is similar. After running this macro, there are no initial selections displayed. I have attached a macro to the dropdownchange event, that reads the selections in both dropdowns
其中“books”是一个包含新值的数组。带有“作者”的另一个下拉列表的代码类似。运行此宏后，不会显示初始选择。我在 dropdownchange 事件中附加了一个宏，它读取两个下拉列表中的选择
Set books = Worksheets("Report").DropDowns("DropDownBook")
bookSelect = books.List(books.ListIndex)
and does the necessary filtering. Problem is, if I select an author, the macro will break in the second line of code given above with
并进行必要的过滤。问题是，如果我选择一个作者，宏将在上面给出的第二行代码中中断
Run time error '1004':
Unable to get the List property of the DropDown class
and if i select a book, it will do the same for authors. I guess this happens because there isn't an initial selection in the boxes, but I can't seem to find a way how to make one in code. I have tried
如果我选择一本书，它也会为作者做同样的事情。我猜这是因为框中没有初始选择，但我似乎无法找到如何在代码中进行选择的方法。我试过了
selectBook.ListIndex = 0, selectBook.Value=0 etc.
, but nothing has worked. Am I missing something obvious? Any help will be greatly appreciated.
，但没有任何效果。我错过了一些明显的东西吗？任何帮助将不胜感激。
回答by MatAff
I realize it's been a while since this question was posted, but since it doesn't have an answer I'll do my best to provide one.
我意识到这个问题发布已经有一段时间了，但由于它没有答案，我会尽力提供一个。
Rather than adding the items one by one you can add them all at once. First create a 'booklist' with all options separated with commas, and then add this to a cell. See the example below.
您可以一次添加所有项目，而不是一个一个地添加项目。首先创建一个“书单”，所有选项用逗号分隔，然后将其添加到单元格中。请参阅下面的示例。
' Create option list
books = Array("The Very Hungry Caterpillar", "A Christmas Carol", "Ulysses")
booklist = Join(books, ",")
' Set drop down list
With Sheets("Books").Cells(1, 1).Validation
.Delete
.Add Type:=xlValidateList, Formula1:=booklist
End With
You can wrap this into a separate procedure that you can call whenever you want to add dropdown dynamically.
您可以将其包装到一个单独的过程中，只要您想动态添加下拉列表，就可以调用该过程。
Sub SetDropDown(cellRef As Range, valueArray As Variant)
' Declare variables
Dim valueFormula
' Create formula
valueFormula = Join(valueArray, ",")
' Set dropdown
With cellRef.Validation
.Delete
.Add Type:=xlValidateList, Formula1:=valueFormula
End With
End Sub
