如何在Excel（VBA）中通过代码设置下拉选择？

Question

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

Set selectBook = Worksheets("Report").DropDowns("DropDownBook")
selectBook.RemoveAllItems

For Each b In books
        selectBook.AddItem (b)
Next

其中“books”是一个具有新值的数组。与“authors”下拉菜单相关的代码类似。 运行此宏后，不会显示任何初始选择。我已经将一个宏附加到下拉菜单更改事件上，该宏读取两个下拉菜单中的选择。

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.

我已经尝试了很多方法来解决问题，但都没有成功。我是否忽略了一些显而易见的东西？非常感谢您的帮助。

- user2808401

---

奇怪，它在我这里可以工作... - Siddharth Rout

可以让我看看你的文件吗？如果可以的话，请将它上传到任何文件共享网站并在此处分享链接。 - Siddharth Rout

很不幸，这是业务敏感信息，因此我无法公布真实文件，也没有仿制出现错误的模型。哪个选项应该可以工作？.ListIndex = 0？ - user2808401

这样... 还有这个 bookSelect = books.List(books.ListIndex)。 - Siddharth Rout

谢谢提供示例代码。我已经弄明白如何让它工作了。应该使用selectBook.ListIndex = 1来选择第一个项目（我仍然觉得这很奇怪），在选择索引之前而不是之后使用Application.ScreenUpdating = True。 - user2808401

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

- MatAff

---

感谢您的代码。 我想在顶部添加一个初始化，因为我的VBA版本需要这样做。 Dim books As Variant Dim booklist As String - Nelson Dinh