Drop-down cells

Cities worksheet

The Cities worksheet contains a small list of cities and their timezones relative to UTC (the timezone that used to be called GMT).

The worksheet uses header rows and columns so that the code can reference cells using meaningful names:

• Header column: The city column
• Header row: The row with the labels 'city' and 'timezone'.

Timezone Selector worksheet: adding dropdowns manually

The Timezone Selector worksheet has two important cells linked by a formula using a user code function:

Cell B1

Has a dropdown list populated with a list of cities from the validation range Cities!$A$2:$A$6. This prevents the passing of bad time values to B2.

Cell B2

Has the formula =timezone(B1).

The timezone function

This is defined in the pre-constants user code. When called with a city name, it looks up and returns the appropriate value from the 'time' column on the 'cities' worksheet:

def timezone(city):
    cities = workbook['cities']
    return cities['time', city]

Menu calculator: adding dropdowns with user code

The post-formula user code shows how user code can create and control a simple drop-down. It extracts a comma-separated list of ingredients from cell B2 in the Menu calculator worksheet, and then generates a drop-down menu, combining each ingredient with generous helpings of spam to create each dish.

The user code that sets up the dropdown uses the DropdownItems property:

menu.DropdownItems = ['Spam, spam, %s and spam' % ingredient for ingredient in ingredients.Value.split(', ')]

In this case, the code sets DropdownItems to a list of strings, but it can be set to any kind of Python iterable or a CellRange, and can contain any kind of object.