[Calc] Workbook events sheet management

[mwpowelllde]

I need to know, are the workbook events I can assign a macro towards.

I have the sheet events down, but when I create a new sheet, duplicate, whatever, that sheet also needs the benefit of the sheet events I installed on the first sheet.

[fpy]

question being if you even read answers

Setting array formula Err:508

It might be better if you describe what you want to achieve (with an example file)… Maybe there is one (or more) other way…

[mwpowelllde]

I started by setting a sheet event to begin with. That does well enough.

When I duplicate a sheet, I want to set the same EH for that sheet.

I’m looking into perhaps Activate document events as a way to inspect workbook or ThisComponent.Sheets events.

Maybe as indicated there are more or better ways of connecting those dots.

Edit: Really, though, I just need a “new sheet” or “sheet duplicated” event, if I can find it.

[mwpowelllde]

To clarify, prototype sheet, I did this manually, from Sheet > Sheet Events menu.

How do I do that in code, for new sheets, duplicated sheets, etc.

I need a way of connecting with the workbook sheet events, if possible. Alternatively, I am exploring a slightly more circuitous route via activated documents, but this does not seem quite right either.

[mwpowelllde]

Any constructive ideas? Thoughts? Events I might look into otherwise connecting these dots?

Best.

[Zizi64]

@mwpowelllde

Any constructive ideas?

Read Andrew Pitonyak’s books, and use the Google:
https://www.pitonyak.org/oo.php
.
.

How to programmatically get/set a form control's events

OK, thanks to Mauricio for pointing me in the right direction to get this figured out. The confusing part was that the events assigned to a form control object are not (as one would presume) contained within the control itself, rather its parent, the form object. Perhaps in the future the control object will have additional properties and methods for viewing, getting and setting events associated with it. While Python is a very popular way to run macros in Libre Office, this discussion uses B…

How to assign a macro to an event by a macro

Hello, Do not under the reasoning (or even some logic - options form?) of why you are attempting this. Seems like a security issue lurking. Edit: Regardless, your sample is not comparable to the request. It is a Event for a sheet and in comparison to modifying a form or control event, it is quite different. You can access the Base and Form events with your code. Should be able to access with ThisDatabaseDocument.Events.replaceByName("CHOSEN_EVENT", Prop()) End Edit For Form controls (a…

[mwpowelllde]

Thank you for that. That’s one half the question. The front side of it is, which document or other events might I be subscribing to in order to resolve either addition, duplication, etc, of a sheet? Alternatively, document being active may be an adequate fall back position. Again, I do not know the best possible way here, just asking the question, if there are suggestions, I’m happy to consider all. Thanks so much…

[Zizi64]

Do you want to create a new Sheet automatically based on some conditions/event of the old sheet?
Or do you want to assign some macro to an event of a new sheet automatically (after it has been created manually)?

[mwpowelllde]

Good questions, thank you.

So, the vision I have for this workbook, all the sheets, each of them should serve the same purpose. Perhaps there might be differentials based on sheet naming conventions, but overall, yes, when I see a new sheet, duplicate, etc, I want to attach macros strategically for that sheet.

In terms of relationships to old sheets, no; each of the sheets stand apart from each other.

Hope that helps clarify a bit my objectives with the workbook, EH, etc.

[Villeroy]

The script events for the sheet tabs are poorly implemented. They don’t pass any event struct, which would allow to identify a calling sheet object.

[mwpowelllde]

The script events for the sheet tabs are poorly implemented. They don’t pass any event struct, which would allow to identify a calling sheet object.

Of course, of course, I was somewhat finding that to be the case as well, the event args and so forth, however, be that as it may. I at least want to use an appropriate EH as a trigger source, then double back into ThisComponent as an alternative. Maybe there are better ways, which is what I’d like to gain awareness.

[Zizi64]

@mwpowelllde

when I see a new sheet, duplicate, etc, I want to attach macros strategically for that sheet.

It must be a Document scope (level) Event, because the old Sheets will not be changed when a new one is created. The peredefined Document-level and Application-level Events are available in the Events TAB of the Customize panel.
And you can create new Events (see the documents linked in my previous posts)
.
Have you installed one of the excellent object inspection tools: MRI or XrayTool? With these tools you will able to inspect the programmng objects like the documents, sheets, events and many others. The tool will list the available properties, methods, and others…

[Zizi64]

Here is my new sample file. The new macro (assigned to a Button) will create a new sheet named “SheetX”, and then it will assign a macro to the the desired Sheet event.

SheetEvent_Sub_AddSheet_AssignMacro.ods (18.4 KB)

[JohnSUN]

@Zizi64

new sample file

Good job my friend! Just a small typo - no need for that “1”

[Zizi64]

Sorry, I have not tried that the assigned macro works or not on the new sheets…
Thank you!
.
Here is the fixed version:
SheetEvent_Sub_AddSheet_AssignMacro2.ods (18.4 KB)

[mwpowelllde]

Have you installed one of the excellent object inspection tools: MRI or XrayTool? With these tools you will able to inspect the programmng objects like the documents, sheets, events and many others. The tool will list the available properties, methods, and others…

I have not, but I’ll look into it. Currently I’d say limping along on the standard built in watch, which is useful, to a point.

[mwpowelllde]

I’m probably missing something, but… I opened the fixed version, and I see the EH macro code there. But I do not see any events connected when I open, Tools > Customize … which event is it that I should be thinking about there?

[mwpowelllde]

Okay so to clarify further, no, I do not want to use a form button. Rather I want to put an appropriate doc EH into action, looking for a handful of realistic suggestions.

[Zizi64]

@mwpowelllde

Okay so to clarify further, no, I do not want to use a form button.

My sample is for ASSIGNING A MACRO to a SHEET EVENT. The sample macro will launch on the Button Event.
YOU must find or create an another Event (of the document) for automatic launching the macro to create a new sheet, and assigning the parsing macro to the sheet event.
.
What event or condition do you want to use for this task?
How many new Sheets do you want to add to the document (only one or more)?
.
Be careful: most of the the events can occur many times, therefore the assigned macros will run periodically (Recalculate event, Sheet changed event and others will be raised more than once.)

[sokol92]

As far as I know, Calc does not fire document events when the user adds or deletes sheets. In comparison, Excel has an Workbook.NewSheet event when a new sheet is added, and Worksheet.BeforeDelete when a sheet is deleted.
A (theoretical) workaround is to use the XUndoManagerListener listener. This is a long way around this, and has a number of quirks:

1. The document’s Undo mechanism may be disabled or locked.
2. Undo events have localized titles that need to be defined for the current user interface.

As always in such cases, we need to consider whether the potential payoff is worth the effort.