UNO API or Basic for adding a Macro to a Sheet Event

[Bal]

I have a sheet with a macro added to the Activate Document ("OnFocus") event. However, when the sheet is copied to a new sheet, the macro is not attached to the Activate Document. I would like to do this programmatically rather than make the user do it.

The Dispatcher uno:TableEvents will open the Assign Action dialog for the user to add the macro to the Activate Document event but I cannot find a set of arguments to pass to this API that will add the macro. Regardless of the arguments I've tried to pass, it seems to only open the dialog.

My questions are: 1) Is there a set of Name / Value arguments I can pass to uno:TableEvents that will do this or 2) Is there another API to use to do this and if so, what are the Name / Value arguments to do this.

Thanks for any help.

[Zizi64]

Please upload your sample file and the macro code here.

[Bal]

In the code below, sEvents uses the active sheet nSheet. getByName("OnFocus") returns the structure of the OnFocus event IF there is one. Otherwise, it returns an error. An error occurs if no macro is attached and is handled by ErrorHandler.

Inspection of the structure of sEvents when the macro IS present shows 2 elements whose Name & Value I supply as args1(1) and args1(2). All this does is open the Assign dialog as I mentioned before.

Code: Select all

On Error Goto ErrorHandler
sEvents = nSheet.Events.getByName("OnFocus")

ErrorHandler:
Reset
dim args1(2) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ElementNames" : args1(0).Value = "OnFocus"
args1(1).Name = "EventType" : args1(1).Value = "Script"
args1(2).Name = "Script" : args1(2).Value = "vnd.sun.star.script:Standard.Module1.Upload_Inventory?language=Basic&location=document"
dispatcher.executeDispatch(document, ".uno:TableEvents", "", 0, args1())
Exit Sub

[JeJe]

Why not use a template? Macros and event bindings in the template will be preserved in the newly created document.

[Bal]

I've never worked with templates before but saving the sheet as a template doesn't seem to preserve the event binding. So a couple of questions come to mind. First, what needs to be done to save the sheet as a template that DOES preserve the event binding? Second, the workbook is available for download as a ZIP file. If I include a template in that file it would need to be available to the user without the need for a great deal of user interaction. Second, since this particular sheet has references to other objects in the workbook, it will contain errors when stored as a template and not easily resolved once placed in the workbook, so maybe not the happiest of solutions. And finally, I don't see a way to insert the template as a sheet in the current workbook so it would seem that for the user, it's a lot more work than a simple right-click for Move/Copy the sheet.

[JeJe]

It worked for me... I created a simple spreadsheet template (attached - change the extension from .zip to .stc, .stc files not accepted here)... it has a macro that runs when a created document is activated, a simple message box. Saving and reopening the document I still got the message box.

Edit: so you might have a simple macro that creates a new document from the template then copies the sheet to it perhaps?
Edit2: alternatively you could have a macro that copies the document and deletes all the unwanted sheets?
Edit 3: see correction below

[Bal]

As far as I can tell, there is no .ods or .ots file anywhere in this zip and a TON of folders, many of which are empty. The XML docs produce a General Error when I try to open then with Calc so I don't know what they contain. I suspect the reason it "works" for you is that the template & the macro to which it is bound are in the same structure. When I created my template with an event binding to a Basic subroutine under the workbook name in Standard / Module1, it did not become part of the template.

[JeJe]

rename the file from Untitled 1.zip to Untitled 1.ots

I had to change the extension as the site doesn't let me upload a file with the stc extension but does allow .zip.

Edit: An OO file is a ton of folders zipped but given a different extension from .zip

Edit2: corrected for correct extension

Edit 3: see correction below

[JeJe]

Oops Sorry I should have said rename the extension .ots but it does let me postthat... don't know how I managed to create that botch up.

[Lupp]

Code: Select all

Sub copyOnFocusEventFromSheetToSheet(pSourceIndex, pTargetIndex)
REM The sheet indices are zero-based here!
doc         = ThisComponent
sheetSource = doc.Sheets(pSourceIndex)
sheetTarget = doc.Sheets(pTargetIndex)
sheetTarget.Events.replaceByName("OnFocus", sheetSource.Events.getByName("OnFocus"))
End Sub

Adapted based on https://ask.libreoffice.org/en/question ... y-a-macro/. Many thanks to Pierre-Yves Samyn who unfortunately seems to no longer contribute to the forums. Hope he is well.

Also:
Since the word "sheet" (short for "spreadsheet") is indispensable for use with its actual meaning of "one single spreadsheet contained in a spreadsheet document" we must not use it with a different meaning like "spreadsheet document".

Unfortunately we often find the misuse, and tend to understand questions in a wrong way. In this case I was caught as also "JeJe" may have been. Everybody learn from this to try to be even clearer about his intentions as he anyway is.

In this case the scandalous replacement of what's correctly named "OnFocus" in the API to "Activate Document" for the UI concerning SHEET events was specifically misleading. The current question seemed to be about "document events" therefore, instead of about "sheet events".
I will never understand this idiotic kind of renaming hard coded in the software which is to find in many places. However, it may be too hard a task for the developers to fix the issue. It would be extremely meritorious on the other hand, imo.
"One thing - one name!" must be the guiding principle in the realm of programming/software.

[RoryOF]

Many thanks to Pierre-Yves Samyn who unfortunately seems to no longer contribute to the forums. Hope he is well.

He was active on another list a few minutes ago.

[Bal]

I appreciate the code snippet and it would work just fine if the user does not delete or rename the sheet SheetSource which they often do. Even if they do not, it can appear anywhere in the workbook so it has no "constant" pSourceIndex I'm afraid. These are among the reasons I was looking to recreate it in some manner. Perhaps there is a way to create the structure of the getByName("OnFocus") so it could be used with the replaceByName call? That would work I think.

I'm apologize for any confusion over the use of the word sheet in my original post. I meant a single sheet & thought this was clear in context. Names should be consistent and unambiguous but as you point out, often are not. The Assign dialog for a sheet specifies Activate Document which is the "OnFocus" event for that sheet but for a workbook the Tools > Customize > Events dialog also specifies Activate Document which is the "OnFocus" event for the workbook itself. The workbook "OnFocus" event does not seem to fire as you change between sheets in the workbook.

[JeJe]

There is this thread if you haven't seen it, "How to copy Sheet with Event macro via vba code" :

viewtopic.php?f=20&t=74454

[Lupp]

Unfortunately I am not a sorcerer, and I cannot write code for spreadsheet documents working as expected under any circumstances, even if someone changed the structure of the document or - in the extreme case - the code itself.
I am afraid nobody could do such a job.

On the other hand I lack information I would need to mimick a sorcerer in this specific case.

If you tell me that all the sheets of the document over all its lifetime shall have the same specific macro associated with their events OnFocus, I can tell you how to ensure that. If you tell me something else sufficiently unambiguous I may also find a solution ...
I will not try to change the source code of "our" software, and I will not find a hidden event thrown OnCopySheet which - if existing - would allow to intercept it for the purpose under discussion. I think there is none.

(My code anyway does not identify the source sheet by its name, but by its index.)

And: The source of the confusion wasn't you, but bad practise adopted by others, and misleading me and JeJe.
Concerning the naming we seem to agree. Arbitrarily changing the name of a specific thing is bad. Doing it in a gravely misleading way ("Activate Document" for a SHEET event) is worse. That's the developers' fault, however.

[Bal]

Thanks to you all for your help. I have decided that since there is 1 hidden sheet in the workbook the user must maintain in the book for other parts to work successfully, including the type of sheet where the OnFocus event comes into play, I've decided to bind the OnFocus macro I need there & copy it from there as Lupp suggests. The post JeJe refers to has code that would work as well. If the user deletes the sheet, that's on them as much as changing the code would be. Under either of those conditions the "sorcery" of the programming is undone and so be it. One cannot be expected to protect people from themselves if they do what they've been instructed not to do.

[Villeroy]

Contrary to "sheet protection" which protects the locked contents of a sheet, the similarly misleading term "document protection" protects the collection of sheets. With "document protection" you can not move, delete, (un-)hide, rename nor add sheets. You would have to write a simple macro to allow adding sheets.