Reasoning
The dropdown built into Excel requires a range of data to populate the dropdown list. This might be an appropriate situation if the spreadsheet is a one-off. In my work, the dropdown list would more appropriately be generated in my PyXLL program.
Having the dropdown and its data provided by a PyXLL function allows:
- central control of the list
- easy updating
- unrestricted placement of the dropdown — any spreadsheet, any tab, any cell
The function is written in PyXLL. The data for the dropdown is taken from a Python dictionary. The dialog is built using PyQt5: the dialog is a QInputDialog built using a QVBoxLayout.
The function allows:
- a named reference
Example: apples —> would reference my_dropdown_lists[‘apples’] - a regex filter for the dropdown list
- an initial value for the dropdown list
PyXLL function information
PyXLL macro functionsSpreadsheet Setup
Legend
Assign macro to button
The ‘entry cell’ is shaded (for clarity only).
The optional ‘initial value’ entry is up one row from the entry.
The optional ‘regex filter’ entry is up two rows from the entry.
The optional ‘name reference’ entry is up three rows from the entry.
To set up the function, I assign my macro function to a button; then I label the button with text.
To use the function, I simply select a cell and click the button. The dropdown appears and I select the desired entry and click OK. On the first use, the ‘initial value’ is taken from the cell above. After each selection the cell above contains the last selection made.
After a selection, a new regex filter might be selected. If the last value becomes invalid then the initial value index position is set to zero (start of filtered list).
Dropdown in Action — Video
In the video, the default is ‘three letter names’ if there is no ‘name reference’ entry.
Regex example
The ‘regex filter’ entry is ^.*a.* which restricts strings to those with at least one ‘a’. Therefore, only these apples appear in the dropdown: ‘gala’, ‘macintosh’, ‘granny smith’, ‘pink lady’.
Python code excerpt
..
@xl_macro
def select_from_PyXLL_dropdown():
"""
Execute a PyQt dialog based on a list from a dictionary
"""
xl = xl_app()
my_dropdown_lists = {
"apples": [
"gala",
"honeycrisp",
"macintosh",
"cosmic crisp",
"fuji",
"granny smith",
"pink lady",
],
"countries": [
"Uganda",
"Ukraine",
"United Arab Emirates",
"United Kingdom",
"United States of America",
"Uruguay",
"Uzbekistan",
],
"three letter names": ["bob", "joe", "leo", "ned", "ray", "sam", "tom"],
"cars": [
"alpha romeo",
"buick",
"chrysler",
"dodge",
"elantra",
"fiat",
"gremlin",
"honda",
"isuzu",
"jaguar",
"kia",
"lincoln",
"mazda",
"nissan",
"opel",
"pontiac",
"quattro",
"rolls royce",
"saturn",
"toyota",
"uplander",
"volkswagen",
"wrx",
"xantia",
"yaris",
"zagato",
],
}
r_entry = xl.Selection
# next row up
r_init_last_range = r_entry.GetOffset(RowOffset=-1, ColumnOffset=0)
# next row up
r_filter_value = r_entry.GetOffset(RowOffset=-2, ColumnOffset=0).Value
# next row up
r_named_variable = r_entry.GetOffset(RowOffset=-3, ColumnOffset=0).Value
...
