Nested drop-down menu list

Hi:

I am using LO 7.3.2.2 (x64) on desktop Win10 Home.

I want to fine tune a LO Calc file by adding a column that includes a Category drop-down menu, but I find that I might have need for subcategories, in some cases, to avoid a very long list of items.

I recall viewing a tutorial about what I am calling “nested drop-down menu list”, whereby if an option in the parent drop-down is selected, a second, a third, … etc. drop-down list is then opened, if they exist. For example, if one option is utilities, a second drop-down is opened showing, e.g. telephone, electricity, heating fuel, water, etc., as further, optional, selections.

Unfortunately, I cannot recall if the tutorial was for Excel or Apache Open Office or Libre Office.

Can somebody point me to a Libre Office video tutorial or within the LO Help that covers “nested drop down list”? “Nested drop-down menu list” is most probably an incorrect key word text string.

Thanks for reading this.

Would be something like this ?

Or this one?

…or may be this

It is worth mentioning that @karolus’s solution (he’s referencing his own) is the forms control version of the German video referenced by @PKG in one of the links (Mehrere von einander abhängige Dropdownlisten /Auswahllisten (LibreOffice Calc) - YouTube) which uses data validation drop lists for the same basic effect. Both are very clever.
Neither would seem to handle having a non-leaf-node list that coincidentally has an entry of the same name as exists within another non-leaf-node list, short of an arbitrary change in one of the lists. However, in either case appending the parent list selection within the Python macro or before the INDIRECT() in the data validation reference field (and setting up named ranges accordingly) ought to disambiguate.

:slightly_smiling_face: Flatten an array with data = (dat[0] for dat in data) in Python. Hmmm…maybe I’ll be won over to Python yet.

And that specific was a humble “workaround” before i was able to master this javaesque Api. the updatet Sourcecode:

from com.sun.star.beans import NamedValue

def update_other(button):
    """
    triggert by Event: 'Modified' of Listbox_button
    search for the Named_Range addressed by actually selected Item,
    and updates
    'other_button's ListEntrySource to the name_range…RangeAddress
    
    with credits to @ Villeroy:
    [increase Listboxes]
    (https://forum.openoffice.org/en/forum/viewtopic.php
    ?f=20&t=65127&p=289137&hilit=+ListEntrySource#p289074)
    """
    
    OTHERBUTTON = "List Box 2"
    
    control = button.Source
    item = control.SelectedItem    
    controls = control.Model.Parent
    doc = controls.Parent.Parent
    
    named_ranges = doc.NamedRanges
    name_range = named_ranges.getByName(item)
    
    name_val = NamedValue()
    name_val.Name = "CellRange"
    name_val.Value = name_range.ReferredCells.RangeAddress
    
    list_source = doc.createInstance("com.sun.star.table.CellRangeListSource")
    list_source.initialize((name_val,))
    
    for chield in controls:
        if chield.Name == OTHERBUTTON:
            chield.setListEntrySource(list_source)
            return
1 Like

My thanks to those that provided useful replies to my task. I am much closer now to solving .
I have learned that there is a difference between a “list box” & a dynamic/dependent drop-down list using Data>Validity>Allow Cell range/List… and still learning.

What I have called “nested drop-down menu list” is indeed related to using the Data>Validity path.

I will try out some of the suggestions given.

Thanks again.