如何从Excel中的代码(VBA)设置下拉列表选择

Question

我正在尝试为Excel表格制作过滤器。我有 2 个下拉框，其中的项目列表动态添加到刷新表时调用的宏中。

Set selectBook = Worksheets("Report").DropDowns("DropDownBook")
selectBook.RemoveAllItems
For Each b In books
        selectBook.AddItem (b)
Next

其中"books"是具有新值的数组。带有"作者"的另一个下拉列表的代码类似。运行此宏后，不会显示初始选择。我已经将一个宏附加到下拉列表更改事件，该宏读取两个下拉列表中的选择

Set books = Worksheets("Report").DropDowns("DropDownBook")
bookSelect = books.List(books.ListIndex)

并进行必要的过滤。问题是，如果我选择一个作者，宏将在上面给出的第二行代码中中断

Run time error '1004':
Unable to get the List property of the DropDown class

如果我选择一本书，它也会对作者做同样的事情。我想发生这种情况是因为框中没有初始选择，但我似乎找不到如何在代码中制作一个的方法。我试过了

selectBook.ListIndex = 0, selectBook.Value=0 etc.

，但没有任何效果。我错过了一些明显的东西吗？任何帮助将不胜感激。

Answer 1

我意识到这个问题发布已经有一段时间了，但由于它没有答案，我会尽力提供一个。

您可以一次添加所有项目，而不是逐个添加项目。首先创建一个"书单"，所有选项用逗号分隔，然后将其添加到单元格中。请参阅下面的示例。

' 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

您可以将其包装到一个单独的过程中，只要您想要动态添加下拉列表，就可以调用该过程。

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