I have a simple Calc macro that writes to a cell on save, and it works well. However, it is also trying to run whenever I edit a document in Writer. What do I need to do to limit this macro to running only in Calc?
Please reedit your Q here and attach the macro code that you cite.
Hello,
This is happening probably because of the manner in which the macro was set to execute. The macro was likely placed in My Macros->Standard->Some Module
. This macro was then tied to an event such as Save Document
. It was also likely marked as Save in: LibreOffice
. With this saved in LibreOffice, any module within LO will execute this macro (Writer, Calc, Base, etc.). The other choice is to save in the document itself but then only that document would have the macro execute.
If you want this macro to execute only for this particular document, delete the event from LibreOffice
and add it back in Save in: XXXX.ods
. Now only this document will have the macro executed.
If, however, you want this to work across ALL your Calc documents, leave the event setting as is but add a line of code at the beginning of your macro:
If NOT ThisComponent.supportsService("com.sun.star.sheet.SpreadsheetDocument") Then Exit Sub
The will bypass the code for any module other than Calc.
Thanks very much… your assumptions are spot on, and your explanation makes sense.
I’ve been able to remove the macro from My Macros->Standard->Module1, and create it at XXXX.ods->Standard->Modules->Sheet1. I’ve also removed the old macro from the “Save Document” event in Customize, and assigned the new macro to that event. However, that doesn’t seem to work at all… the macro doesn’t update cell A1 in Sheet 1 or any other sheet. It should update A1 when any sheet is saved. What did I miss?
OK. Again you have placed me at a disadvantage as this time I must guess at the code you have. Whether the macro is in My Macros or in the document should not be a factor. In My Macros it is available across LO & in the document it can only be used by the document. Just because it may be in My Macros doesn’t mean other WILL execute it; only that they can. More …
This macro:
Sub SetTimestamp
If NOT ThisComponent.supportsService("com.sun.star.sheet.SpreadsheetDocument") Then Exit Sub
oSheet = ThisComponent.Sheets.getByName("Sheet1")
myRange = oSheet.GetCellRangeByName("A1")
myRange.setValue(NOW())
End Sub
was tested and works in either My Macros or from within the document. It places the current date/time in Sheet1
cell A1
regardless of the sheet you may be working on when the save is done.
Ah yes, including my macro would have been good… sorry. Here it is:
Sub SaveDateTime
Dim my_sheet as object
Dim my_cell as object
my_sheet=thiscomponent.getcurrentcontroller.activesheet
my_cell = my_sheet.getCellbyPosition(0,0)
my_cell.String = Now
End Sub
I saved it in XXXX.ods->Standard->Modules->All. “All” is a module I created, of course.
When I originally added it to the document, it defaulted to the Sheet1 module, which also failed.
I can of course use the macro you provided – and thanks for that – but I’d really like to understand why the one I have isn’t working. I might actually learn something!
The macro itself works as is if what you want is the current date/time in cell A1 of the current sheet.
What is confusing is where you placed the Macro.
So it is placed in the document - that is XXXX.ods
In the Library name Standard
Now the confusion. In a Library you have modules where you place the code. Your comment seems to state a module named Modules
and another module within that named All
. Can’t have modules within modules!
I understand what you’re saying, but what I see in Calc doesn’t line up with what you said about modules.
At the top level of the Basic Modules window was “My Macros”, “LibreOffice Macros”, and “XXXX.ods”. If I clicked on the + next to “XXXX.ods”, I saw “Standard”, “Forms”, and “Modules”. Clicking on the + next to “Modules” revealed “Sheet1”, which I didn’t create - it was already there.
I then created “All” under “Modules”, and stored my macro there. Isn’t that correct place to put a macro?
Sounds right but your description is incorrect:
XXXX.ods This is the document
Standard This is the Library
Forms Another Library
Modules Another Library
Sheet1 A module in a Library - click on this one shows macros in module
So it appears you created a new module under Modules
named All
. So where you stored the macro war really in: XXXX.ods->Modules->All
. So I just set my sheet that way & have no problems running your code.
First of all, thanks for the explanation of the hierarchy. I understood that “Sheet1” and “All” were modules, but didn’t know that the items shown as “Standard”, “Forms”, and “Modules” were referred to as Libraries.
So I did have my macro in the right place, but for some reason it wasn’t working. I’ll keep examining things on my end because it has to be something I’m missing.
Thanks again for all the assistance. I have indeed learned a lot through this dialog.