How to set dropdown selection from code in Excel (VBA)

Question

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.

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

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.

Solution

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

Comments

[user2808401]

Thanks for the sample. I figured how to get it working. It should be selectBook.ListIndex = 1 to select the first item (which I still find odd) and Application.ScreenUpdating = True before I selected the index, not after.

[Nelson Dinh]

Thanks for your code. I want to add an initialization on top because my VBA version requires so. ` Dim books As Variant Dim booklist As String `