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
[Solved] Find Insert Sheet event
[Solved] Find Insert Sheet event
Last edited by MrProgrammer on Wed Sep 21, 2022 9:20 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved] -- MrProgrammer, forum moderator
Reason: Tagged ✓ [Solved] -- MrProgrammer, forum moderator
LibreOffice 6.1.5.2 on Debian 10 with XFCE
Re: Finding Insert Sheet event
There seems to be no such event.
c.s.s.util.XChangesListener listens to inserted/removed rows and columns but not sheets.
c.s.s.util.XChangesListener listens to inserted/removed rows and columns but not sheets.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Finding Insert Sheet event
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.
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.
LibreOffice 6.1.5.2 on Debian 10 with XFCE
Re: Finding Insert Sheet event
Ask them.ken054 wrote:I wonder how they do it then.
IMHO, splitting equally structured data across separate sheets is a mistake.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Finding Insert Sheet event
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 workVilleroy wrote:IMHO, splitting equally structured data across separate sheets is a mistake.
Thanks again,
Ken
LibreOffice 6.1.5.2 on Debian 10 with XFCE
- MrProgrammer
- Moderator
- Posts: 4913
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Finding Insert Sheet event
Hi, and welcome to the forum.
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
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.ken054 wrote:However I need it to only run when a sheet is added or, less likely, deleted.
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
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: Finding Insert Sheet event
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.
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.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
Re: Finding Insert Sheet event
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).
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).
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community