Excel VBA to select from drop-down list

0
votes

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.

I cant seem to figure out how to use VBA to choose the first option on the list.

Any help appreciated.

1
excelvba

1 Answers

1
votes

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