Help Needed - Python - Hide/Unhide Sheets/Rows & Validity Rules

Hi all,

I’m migrating from Office 365 to LibreOffice (LO), and I want to translate my VBA code to Python and use it in macros in Calc.

I’m using the latest LibreOffice 24.2 on MacOS 14.5. My Python installation is 3.11.9 (miniconda).

I’m having problems with the code below, that either reports an error or does nothing at all.

from __future__ import unicode_literals
import uno

def problematic_code():

    # Get Current Document
    doc = XSCRIPTCONTEXT.getDocument()

    ## CODING PROBLEM 1

    # The Workbook has 2 Sheets: Sheet1 and Sheet2.

    # If cell A1 in Sheet 1 has value 0, hide 
    # Sheet2 AND hide row 5 in Sheet1.
    # Otherwise, show Sheet 2 and row 5 in Sheet1.

    cell_value = doc.Sheets["Sheet1"]["A1"].getValue()

    # Unfortunatelty, this does not work - returns error
    if cell_value == 0:
        doc.Sheets["Sheet2"].isVisible = False
        doc.Sheets["Sheet1"]["A5"].Rows.isVisible = False

    else:
        doc.Sheets["Sheet2"].isVisible = True
        doc.Sheets["Sheet1"]["A5"].Rows.isVisible = True   


    # CODING PROBLEM 2
    # The Workbook has 3 Sheets: Sheet3 to Sheet5.

    # Cell A1 in Sheet3 can assume values 1 to 3.
    # If Cell A1 in Sheet3 has value of 1, then
    # Cell A1 in Sheet4 will have a validity rule
    # that allows for the values in cell range Sheet5!$A$1:$A$5.
    
    # If Cell A1 in Sheet3 has value of 2, then
    # Cell A1 in Sheet4 will have a validity rule
    # that allows for the values in cell range Sheet5!$A$7:$A$11.

    # If Cell A1 in Sheet3 has value of 3, then
    # Cell A1 in Sheet4 will have a validity rule
    # that allows for the values in cell range Sheet5!$A$13:$A$17.

    cell_value = doc.Sheets["Sheet3"]["A1"].getValue()

    # Unfortunatelty, this does not work - it doesn't return an error though

    if cell_value == 1:
        dv_list = doc.Sheets["Sheet4"]["A1"]
        oValidation = dv_list.Validation
        oValidation.Type = 6
        oValidation.setFormula1("Sheet5!$A$1:$A$5")   

    elif cell_value == 2:
        dv_list = doc.Sheets["Sheet4"]["A1"]
        oValidation = dv_list.Validation
        oValidation.Type = 6
        oValidation.setFormula1("Sheet5!$A$7:$A$11")   

    elif cell_value == 3:
        dv_list = doc.Sheets["Sheet4"]["A1"]
        oValidation = dv_list.Validation
        oValidation.Type = 6
        oValidation.setFormula1("Sheet5!$A$13:$A$17")   

    else:
        # By the way, here I would like to clear the validity rule.
        pass

    return

The error that I got from the hide/unhide bit is as thus:

# com.sun.star.uno.RuntimeException: Error during invoking function problematic_code in module file:///Applications/LibreOffice.app/Contents/Resources/Scripts/python/Example.py (<class 'AttributeError'>: isVisible
#   File "/Applications/LibreOffice.app/Contents/Resources/pythonscript.py", line 916, in invoke
#     ret = self.func( *args )
#   File "/Applications/LibreOffice.app/Contents/Resources/Scripts/python/Example.py", line 16, in problematic_code
#     doc.Sheets["Sheet2"].isVisible = False
# )

Maybe I’m not importing something.

Any help would be appreciated.

All the best,
FMVS

…isVisible != …IsVisible

and later:

oValidation.setFormula1("Sheet5!$A$1:$A$5")   
#should be:
oValidation.setFormula1("Sheet5.$A$1:$A$5")

Good Morning,

Thank you for your input. It fixed problem #1, and I added the correction to the final code.

But problem #2 still persists.

I tested the flags (e.g. cell_value == 1), to confirm that, indeed, the correct value and type are being passed – and they are.

I also commented out the validation type – it does nothing (doesn’t even throw an error).

I looked on other posts in here, and the code is similar.

Maybe it’s bugged?

Again - I’m very grateful,
FMVS

Hello,

I fixed it – I needed to add:

dv_list.Validation = oValidation

Plus, one can clear the validity rule by doing this:

        dv_list = doc.Sheets["Sheet4"]["A1"]
        oValidation = dv_list.Validation
        oValidation.Type = 1
        dv_list.Validation = oValidation 

Here’s the complete code:

# coding: utf-8
from __future__ import unicode_literals
import uno

def problematic_code():

    # Get Current Document
    doc = XSCRIPTCONTEXT.getDocument()

    ## CODING PROBLEM 1
    # The Workbook has 2 Sheets: Sheet1 and Sheet2.

    # If cell A1 in Sheet 1 has value 0, hide 
    # Sheet2 AND hide row 5 in Sheet1.
    # Otherwise, show Sheet 2 and row 5 in Sheet1.

    cell_value = doc.Sheets["Sheet1"]["A1"].getValue()

    if cell_value == 0:
         doc.Sheets["Sheet2"].IsVisible = False
         doc.Sheets["Sheet1"]["A5"].Rows.IsVisible = False

    else:
         doc.Sheets["Sheet2"].IsVisible = True
         doc.Sheets["Sheet1"]["A5"].Rows.IsVisible = True
 

    # CODING PROBLEM 2
    # The Workbook has 3 Sheets: Sheet3 to Sheet5.

    # Cell A1 in Sheet3 can assume values 1 to 3.
    # If Cell A1 in Sheet3 has value of 1, then
    # Cell A1 in Sheet4 will have a validity rule
    # that allows for the values in cell range Sheet5!$A$1:$A$5.
    
    # If Cell A1 in Sheet3 has value of 2, then
    # Cell A1 in Sheet4 will have a validity rule
    # that allows for the values in cell range Sheet5!$A$7:$A$11.

    # If Cell A1 in Sheet3 has value of 3, then
    # Cell A1 in Sheet4 will have a validity rule
    # that allows for the values in cell range Sheet5!$A$13:$A$17.

    # Otherwise, clear the validity rule.

    cell_value = doc.Sheets["Sheet3"]["A1"].getValue()

    if cell_value == 1:
        dv_list = doc.Sheets["Sheet4"]["A1"]
        oValidation = dv_list.Validation
        oValidation.Type = 6
        oValidation.setFormula1("Sheet5.$A$1:$A$5")
        dv_list.Validation = oValidation   

    elif cell_value == 2:
        dv_list = doc.Sheets["Sheet4"]["A1"]
        oValidation = dv_list.Validation
        oValidation.Type = 6
        oValidation.setFormula1("Sheet5.$A$7:$A$11")
        dv_list.Validation = oValidation   

    elif cell_value == 3:
        dv_list = doc.Sheets["Sheet4"]["A1"]
        oValidation = dv_list.Validation
        oValidation.Type = 6
        oValidation.setFormula1("Sheet5.$A$13:$A$17")
        dv_list.Validation = oValidation

    else:
        dv_list = doc.Sheets["Sheet4"]["A1"]
        oValidation = dv_list.Validation
        oValidation.Type = 1
        dv_list.Validation = oValidation       

    return

I hope this code helps someone else.

Thank you so much to @karolus for their contribution :slight_smile:

On to the next coding problem.

Cheers,
FMVS