libreoffice calc python macro data validity list

[Zef]

Hello,
An example of the python code is here:

#!/usr/bin/python3
#-*-coding:utf8-*-

import uno
    
class CalcExamples():
    def __init__(self):
        desktop=XSCRIPTCONTEXT.getDesktop()
        model=desktop.getCurrentComponent()
        if not hasattr(model, "Sheets"):
            model=desktop.loadComponentFromURL("private:factory/scalc", "_blank",
             0, ())
        sheets=model.Sheets.getByIndex(0)
        trange=sheets.getCellRangeByName("B2")
        trange.String="Experiência"
        #ComboBox
        celula1=sheets.getCellRangeByName("B4")
        celula2=sheets.getCellRangeByName("B5")
        celula3=sheets.getCellRangeByName("B6")
        celula1.setValue(2)
        celula2.setValue(3)
        celula3.setValue(5)
        combobox=sheets.getCellRangeByName("B7")
        oValidation = combobox.Validation
        oValidation.setFormula1("$Sheet1.$B$4:$B$6")
        combobox.Validation = oValidation
        return None
   
g_exportedScripts = CalcExamples,

The thing is I have to select the cell “B7” and go to Data → Validity → and on cell range click OK.
I don’t know what is missing to this for me!
This is on a Linux distro.
Thank’s

Thank’s it’s working.

[Ratslinger]

Hello,

It is not clear as to why you need any macro to do this. All can be done without any code. And if it is a matter of dependency, why not just use the Enabled and/or Visible property to allow/disallow access?

Also would be helpful to know what code you have already.

Please do not use an answer to reply. add a comment or edit you original question with additional information.

[Ratslinger]

@Zef,

You have changed your original question of:

Hello I'd like to insert a combobox in a calc cell from a list or from a range of cells, this with a python macro. Is this possible, if so can any one let me know the python code. Thanks Zef

to what is above. What is it you are really wanting? Is it Python code on how to access a combo box? Have you seen the documentation here → LibreOffice Python Scripts Help?

Would like to try and help but not certain just what it is you want.

[Ratslinger]

Edited question to use pre-formatted text for code (toolbar icon to do this).

[Ratslinger]

Please, in the future do not delete previous parts of the question. Only add to it and note as edited section.

Now it turns out that you are not dealing with a combo box. A combo box is a control on a form. You are dealing with data validity and creates a dropdown list.

See replaced answer for working code.

[Ratslinger]

Hello,

Now that it is realized what you really want, took another stab at it:

#!/usr/bin/python3
#-*-coding:utf8-*-

import uno

class CalcExamples():
    def __init__(self):
        desktop=XSCRIPTCONTEXT.getDesktop()
        model=desktop.getCurrentComponent()
        if not hasattr(model, "Sheets"):
            model=desktop.loadComponentFromURL("private:factory/scalc", "_blank",
             0, ())
        sheets=model.Sheets.getByIndex(0)
        trange=sheets.getCellRangeByName("B2")
        trange.String="Experiência"
        celula1=sheets.getCellRangeByName("B4")
        celula2=sheets.getCellRangeByName("B5")
        celula3=sheets.getCellRangeByName("B6")
        celula1.setValue(2)
        celula2.setValue(3)
        celula3.setValue(5)
# Data Validity list
        dv_list = sheets.getCellRangeByName("B7")
        oValidation = dv_list.Validation
        oValidation.Type = "LIST"
        oValidation.setFormula1("$Sheet1.$B$4:$B$6")
# Can use actual values if wanted
#        oValidation.setFormula1('"2";"3";"5"')
        dv_list.Validation = oValidation
        return None

g_exportedScripts = CalcExamples,

Added line to set ValidationType to LIST. That fixed the problem. Also, in commented lines, method to set values directly. Can eliminate getting cells and setting values there if wanted.

Edit:

Per request in comment, similar in Basic:

Sub Main
    oSheet=ThisComponent.CurrentController.ActiveSheet
    oCell = oSheet.getCellRangeByName("B5")
    oCell.setValue(2)
    oCell = oSheet.getCellRangeByName("B7")
    oValidation = oCell.Validation
    oValidation.Type = com.sun.star.sheet.ValidationType.LIST
    oValidation.setFormula1("$Sheet1.$B$4:$B$6")
REM  Use following lines to insert a list of values vs cell range
REM    sList = """4"";""5"";""6"""
REM    oValidation.setFormula1(sList)
    oCell.Validation = oValidation
End Sub

[lonk]

Can we do this combobox in BASIC Macro ?

[Ratslinger]

@lonk,

Don’t see why not. But please, just went through a rough time with wording in comments above. This is NOT a combo box. This is Data Validity list box. Very different.

Also, as I tried to mention in comments, no code is needed to do this. Can be done straight from menu Data->Validity.

See edited answer for Basic code.

[lonk]

@Ratslinger

Thank you so much for your concern.

[lonk]

@Ratslinger

Thank you so much again for BASIC Macro code.

[Ratslinger]

@lonk,

Glad was able to help.