Page 1 of 1

Finding Insert Sheet event

PostPosted: Thu May 13, 2021 10:33 pm
by ken054
G'day there One and All. I hope I've found the correct forum for my question, if not I'm sure someone can point me in the right direction.

By way of background, I've had a bit of experience with Excel VBA and prior to my retirement I was considered in the office to be the XL Guru. That in itself was a sad state of affairs reflecting on the office, rather than my abilities. Suffice to say that I'm untrained but a keen enthusiast.

I'm currently using LibreOffice 6.1.5.2 on Debian 10.

As to my question, I'm looking for a LibreOffice version of XL's "Sheet Add Event". I've written a small code routine that iterates through sheet names and builds a list of those whose names show the year - 2020, 2021, etc. These are then stored one per cell in a particular range on a worksheet. This routine works fine. However I need it to only run when a sheet is added or, less likely, deleted. Since it is more than likely that this will occur only once per year it is best triggered only when the number of sheets changes and so the "Sheet Insert" event would be the most appropriate.

I assume I'll need to build a listener, as per Andrew Pitonyak's excellent document. I further assume that I'll need to access the XSpreadsheetDocument interface, or the com.sun.star.sheet.SpreadsheetDocument service. I've modified Andrew's code to list the document events for Calc (I think correctly) but that doesn't reveal anything about sheets.

In short, I'm not sure where I'm likely to find the "Insert Sheet" event. It unfortunately doesn't appear in the "Tools | Customise | Events" tab and I'm quite lost as to how to proceed from here. If any one has any ideas, please tell me so I can get to work.

Thanks for reading,
Ken :)

Re: Finding Insert Sheet event

PostPosted: Fri May 14, 2021 11:21 am
by Villeroy
There seems to be no such event.
c.s.s.util.XChangesListener listens to inserted/removed rows and columns but not sheets.

Re: Finding Insert Sheet event

PostPosted: Fri May 14, 2021 12:42 pm
by ken054
Thanks Villeroy. I wonder how they do it then. Obviously sheets can be added and deleted (duh) and I would have thought that such an action would be an event. If not, I wonder what the mechanism is. Weird.

Might be easier if I just use a Control Button to use a macro. I've got the "count the sheets" bit, just need to enter or delete a named sheet and then use what I've already got. Still seems strange though.

Thanks again, Villeroy. Much appreciated.

Re: Finding Insert Sheet event

PostPosted: Fri May 14, 2021 1:09 pm
by Villeroy
ken054 wrote:I wonder how they do it then.

Ask them.
IMHO, splitting equally structured data across separate sheets is a mistake.

Re: Finding Insert Sheet event

PostPosted: Fri May 14, 2021 3:11 pm
by ken054
Villeroy wrote:IMHO, splitting equally structured data across separate sheets is a mistake.


Quite possibly, but each sheet contains a simple list of indeterminate length. Separate sheets is just an easy way of labelling each list which will then be used for a summary table on a separate page. Quite possibly I'll build a pivot table out of that summary. I'll just see how it goes and if I have any difficulties I'll just amalgamate to a single sheet. If there's a loud explosion with flames & smoke I'll know it didn't work :lol:

Thanks again,
Ken

Re: Finding Insert Sheet event

PostPosted: Fri May 14, 2021 6:10 pm
by MrProgrammer
Hi, and welcome to the forum.

ken054 wrote:However I need it to only run when a sheet is added or, less likely, deleted.
If you're unsuccessful with finding a Sheet Added event perhaps you can use the Active Sheet Changed event instead. You can tell if a sheet has been deleted by comparing your list with the sheets enumeration. With the standard user interface, one deletes the active sheet, thus the Active Sheet Changed event will be called.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

Re: Finding Insert Sheet event

PostPosted: Sat May 15, 2021 1:21 am
by JeJe
Write a macro which does the sheet insert followed by your other stuff.
Replace the insert sheet item in the insert menu with an item that points to your macro.
Only use that button to insert a new sheet.

Re: Finding Insert Sheet event

PostPosted: Sat May 15, 2021 6:36 am
by eeigor
You can catch sheet insert/remove events with ThisComponent.UndoManager (examples are on this forum), however @Villeroy is right that splitting equally structured data across separate sheets is a mistake. You need to add a field to differentiate your lists, this will make it easier to analyze data in the pivot table. The summary table isn’t needed.

UPD: To get the required list, you need to fill in the autofilter for the corresponding label.
The names of undo actions are localized (UndoManagerEvent.UndoActionTitle).