Start macro based on cell value

[1e_skip]

Hi, I am still starting with macros and I am curious if someone can help me with this. I found a basic macro on the internet that I have adjusted and it works in calc as soon as I start it manually. But not when I choose something in the selection list in cell B89, And the second thing is that it is not variable. With this macro I have to create a new macro for each tab.

--------------------Makro:-----------------------------------------
Sub change_value(cell)
If cell.AbsoluteName = "$WBS-1.$B$89" Then
Select Case cell. String
Case "Persoonlijk": Persoonlijk
Case "Gezamelijke": Gezamelijke
Case "Beide": Beide
End Select
End If
End Sub

Sub Persoonlijk()
doc = ThisComponent
sheet = doc.CurrentController.ActiveSheet
range = sheet.getCellRangeByName("A90:A100")
range.Rows.isVisible = False
range = sheet.getCellRangeByName("A101:A110")
range.Rows.isVisible = True
End sub

Sub Gezamelijke()
doc = ThisComponent
sheet = doc.CurrentController.ActiveSheet
range = sheet.getCellRangeByName("A90:A100")
range.Rows.isVisible = True
range = sheet.getCellRangeByName("A101:A110")
range.Rows.isVisible = False
End sub

sub Beide()
doc = ThisComponent
sheet = doc.CurrentController.ActiveSheet
range = sheet.getCellRangeByName("A90:A100")
range.Rows.isVisible = True
range = sheet.getCellRangeByName("A101:A110")
range.Rows.isVisible = True
End sub

Is there any way to

If cell.AbsoluteName = "$WBS-1.$B$89"

replace it with

doc = ThisComponent
sheet = doc.CurrentController.ActiveSheet

I work with calc version:
Version: 24.2.0.3 (x86) / LibreOffice Community
Build ID: da48488a73ddd66ea24cf16bbc4f7b9c08e9bea1
CPU threads: 4; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win
Locale: nl-NL (nl_NL); UI: nl-NL
Calc: CL threaded

Thanks in advance for the help.

[Lupp]

I’m feeling a faint doubt if this is a topic where the usage of one or another programming language is very relevant.
I would assume the usage of sheet events is the central point.
See attached example:
disask113283_ActionsBasedOnSheetEvents.ods (109.0 KB)

(Do not try this with very old versions of LibreOffice or with OO.o or AOO. They don’t know the scope concept for named ranges and will crash. LibO V 7.5.4 worked well with the example.)

[karolus]

Try the following python-code (linked to Sheet-event: »Content changed«

OBSERVE = "$B$89"

def hide_or_not(event):
    sheet = event.Spreadsheet
    if event.AbsoluteName != f"${sheet.Name}.{OBSERVE}":
        return
    oben = sheet["A90:A100"].Rows
    unten = sheet["A101:A110"].Rows
    choice = event.String
    oben.IsVisible = choice in ["Gezamelijke", "Beide"]
    unten.IsVisible = choice in ["Persoonlijk", "Beide"]

[1e_skip]

Great, , norm thanks for your answer. I’ll try it out right away, and I’ll keep you posted.

I see this is a Python script, And I wouldn’t know how to make a Python Macro. When I go to manage macros → Python I don’t get the option to click new.

[karolus]

@1e_skip

I see this is a Python script

to manage and organize Python you may use the apso.oxt Extension from here

[1e_skip]

@karolus

(linked to Sheet-event: »Content changed«

Hi, thanks a lot for the information. I now figured out why my macro didn’t respond to the dropdown list. I skipped this step ( (linked to Sheet-event: »Content changed« )So the macro I had now works halfway. Only the problem is that it is not variable. But I will test your script for that later. I have also installed the add-on.

[1e_skip]

Hello Karolus

first, apologies for the late response. Unfortunately, I couldn’t mess around with the script you wrote for me earlier. I had to order a new adapter for my laptop because it died. But back to my problem. I had already installed that plug-in you recommended earlier but because not enough experience with Python or how to use that plugin, I did a lot of research today on Python script and LibreOffice Calc. But so far unfortunately without success. But as I said before, I am already grateful to you for the information regarding (linked to tab Event) I did manage to get my Macro to respond to my selection list. So I am at least one step further. Now I just have to try to convert your Python script to a Basic script. So if someone could help me with that, I would be a very happy man.

[Villeroy]

1. Create a directory %APPDATA%\LibreOffice\4\user\Scripts\python\
2. Paste the code into a plain text editor (or Writer).
3. Save the code as plain text with .py file name suffix in that directory.

You’ll find the module under Tools>Macros>Run>"My Macros or Tools>Macros>Organize>Python and in any dialog prompting for a callable macro.

[1e_skip]

@Villeroy

%APPDATA%\LibreOffice

Hi villeroy. Thanks for your response, but does this path also apply to the portable libre office version?

[Villeroy]

Wherever the profile of your portable version is. Have a look at Tools>Options>Paths.

[1e_skip]

Hi villeroy. again thanks for your response,
I have been digging around a bit more, and I don’t know how, but I got it in one of the other ways But it ended up in a very strange location.

C:\Users\s_nol\AppData\Local\Temp\LibreOfficePortableTemp\temp\lu104402jumd1.tmp\2\Scripts\python

And it works exactly as it should now. Apparently I can even do magic, because I had given the old macro script here. I added a sub macro later

Case “None”: macro4

Sub macro4()
doc = ThisComponent
sheet = doc.CurrentController.ActiveSheet
range = sheet.getCellRangeByName(“A90:A100”)
range.Rows.isVisible = False
range = sheet.getCellRangeByName(“A101:A110”)
range.Rows.isVisible = False
End Sub

which apparently just works. Even though it is not processed in that python script. I really don’t know what is happening here.

[1e_skip]

As you can see in the conversation, I solved it by accident. And I even have my 4th sub macro (4th choices) that I had added later in my new macro, that also works in a weird way. Don’t ask me how. hahahahaha, Again, thank you so much for your great help.

[Villeroy]

@1e_skip

C:\Users\s_nol\AppData\Local\Temp\LibreOfficePortableTemp\temp\lu104402jumd1.tmp\2\Scripts\python

This looks like a temporary directory on the C: drive of the current machine. Ask the distributors of your portable version where Python macros can be stored on the removable drive.

[1e_skip]

Oke thanks i wil do.