CODEWITHSUNDEEP
excelvba
amymon87
amymon87

Reputation: 41

Excel VBA to select from drop-down list

I have written a VBA Macro in Excel which creates spreadsheets from a template and updates values for individual spreadsheets based on options in a drop down list.

How can I use VBA to choose the first option in the list?

Upvotes: 0

Views: 15181

Answers (1)

Matteo NNZ
Matteo NNZ

Reputation: 12695

Say your drop down list is named myList, the way to select items programmatically is by using the .ListIndex property of the object. Which means:

myList.ListIndex = j

where j goes from 0 (first element) to n-1 (last element). In your case: 

myList.ListIndex = 0

will select the first item.

IF THE CONTROL IS IN THE SPREADSHEET:

in that case the code is slightly different:

With Sheets("Sheet1").Shapes("Region").ControlFormat
    .ListIndex = 0
End With

Upvotes: 1

Related Questions