Hi.
In LO 7.3.4.2 Id like to create a dropdown (maybe even a listbox) in cell D5 using code - preferably python. Not being able to find any references to it, I haven’t a clue as to do it. Even if I manually create a dropdown list in D5, I can’t find any discussion about this or how to set properties and use methods. Does anyone know how this is done?
the easy part is simply, cloning some existing Cell-Validation to another Cell, but creating or even manipulating by code seems difficult… so far!
def clone_validation():
"""
Example for cloning existing Validation
from current selected cell
to some other cell
"""
doc = XSCRIPTCONTEXT.getDocument()
sel = doc.CurrentSelection
validation = sel.Validation
print(validation.Formula1)
other = doc.Sheets[0]["A12"] #first Sheet A12
other.Validation = validation
# and below try to modify the Formula!!doesnt work so far!!
other.Validation.setFormula1("$Tabelle2.$F$5:$F$11")
# unfortunatly this doesnt work
print(other.Validation.Formula1)
Better Example which creates a DropDown filled with sorted Entries from the Named_Range: other_source
from com.sun.star.sheet.ValidationType import LIST
from com.sun.star.sheet.TableValidationVisibility import SORTEDASCENDING
from com.sun.star.sheet.ValidationAlertStyle import STOP
def set_validation():
"""
Example to create Validation
from scratch,
without need for an existing Validation
"""
doc = XSCRIPTCONTEXT.getDocument()
sel = doc.CurrentSelection
validation = sel.getPropertyValue("Validation")
validation.Type = LIST #mostly used "Dropdown-list"
#.Formula1 accepts CellRangeAddress-Names aswell as Named_Ranges
# or Formulas which returns CellRanges or Lists
validation.setFormula1("other_source")
validation.ShowList = SORTEDASCENDING
validation.ShowErrorMessage = True
validation.ErrorAlertStyle = STOP
sel.setPropertyValue("Validation", validation)
def set_validation_with_attrs():
"""
same as above, but to show howto use python_runtime_functions:
`getattr` and `setattr`
"""
doc = XSCRIPTCONTEXT.getDocument()
sel = doc.CurrentSelection
validation = getattr(sel, "Validation")
setting = {"Type": LIST,
"Formula1": "other_source",
"ShowList": SORTEDASCENDING,
"ShowErrorMessage": True,
"ErrorAlertStyle": STOP}
for attribut, value in setting.items():
setattr(validation, attribut, value)
setattr(sel, "Validation", validation)
Thank you karolus. I am repetitively very appreciative of all the assistance that numerous people provide me in my quest to develop my skills and knowledge of python and LO / LO Basic. It was actually a combo box that I was trying to meddle with via code, but code the info here is useful too. Cheers.
You see, I have some python code that writes data into D7-D12 and I wanted a combo box to be populated by the data in those cells, so that a user can ‘select’ the relevant piece of data from amongst D7-D12 (making a variable hold the choice as a string)
I’ve answered the topic with programmatic creating →→Data→→Validation… , which does very closely the same job as some graphical control like Combobox|Listbox.
For me there exist no, absolutly no serious reason to create all this Extraplunder by Code … so create the stuff from GUI or work it out by yourself.
There was another topic Calc: Change what's in a list box based on what's selected in another list box? which works out to change the Entries of one listbox depentend on another Listbox ( no its not about creating such Listboxes programmaticly )
Think this is due to missing the setting of the validation “Type” to “List”
.
Please see my post here → libreoffice calc python macro data validity list
.
Edit:
@LibreOffice_Mike1 please refer to that same link just posted as that fills the list from selected cells.
I’ve no clue what was wrong, because it clones an existing Validation with .Type LIST
meanwhile it works as expected, no matter which one of the two last codelines below is commented;
#…
doc = XSCRIPTCONTEXT.getDocument()
sel = doc.CurrentSelection
validation = sel.Validation
other = doc.Sheets[0]["A12"] #first Sheet A12
validation.setFormula1("other_source")
#other.setPropertyValue("Validation",validation)
other.Validation = validation
Got it… it needs to set the Formula in the validation-object itself not in other.Validation
#works:
validation.Formula1 = "other_source"
other.Validation = validation
#other.Validation.Formula1 = "other_source"
################
#doesnt work:
#validation.Formula1 = "other_source"
other.Validation = validation
other.Validation.Formula1 = "other_source"
Hi again.
Now that I’ve had some time to explore the reply’s properly, I realise I am unable to be able to do what I want - probably because of my poor question/description, so may I try again?
I want to…
Make a sheet based combo box become populated from various variables some global and some generated from python code when a sheet based button is pressed.
I’m thinking along the lines of
index=len(list_generated_from_function[ ])
ComboBox1.index.value = item for item in list_generated_from_function[ ] step 2.
ComboBox1.index.value+1 = global_variable[index]
OK that syntax may not exist but it encapsulates what I want to do.
And then have a cell, e.g. B11 show the selected item in the ComboBox.
Also I will need a checkbox that will continuously run a python function when it’s checked.
Something like.
While CheckBox1=True:
execute other_function until CheckBox1=False
I have found past discussions of associating variables with form controls not really hitting the mark and I’ve tried dialogs but they have just added another massive layer of complications and errors upon what I’d like to do.
Thanks.
Sorry…
- So far I didnt see any sensefull line of code, written by yourself
- … I didnt see any example.ods with e.g. Combobox[es] attached
- only humpties of vague described individual sophisticate requirements.
I’m done!
You will hardly find much anywhere that may be an exact fit. Most need some adjustment.
.
Sorry but I find your current comment/question vague, open to many other questions and just another measure to search for what may be available just as you stated already:
So it is still not clear as to what you actually want.
I will have to agree with: