Auto Open macro

Dusty50 · March 30, 2021, 12:37am

Hi

It’s been a while, but I was helped previously with this to highlight last cell in column A…it works fine, but can’t figure what to add to it to make it run automatically when the spreadsheet is opened.
Thanks

Sub LASTCELL()
 theDoc   = ThisComponent
 theSheet = theDoc.Sheets(0)
 eCells   = theSheet.Columns(0).QueryEmptyCells
 finRg    = eCells(eCells.Count - 1)
 If finRg.RangeAddress.EndRow<>theSheet.RangeAddress.endRow Then Exit Sub
 target   = finRg.GetCellByPosition(0, 0)
 theDoc.CurrentController.Select(target)
 End Sub

Dusty50 · March 30, 2021, 12:43am

I can’t figure why it posted the way it did so I’ll try another way:

Sub LASTCELL()
 theDoc   = ThisComponent
 theSheet = theDoc.Sheets(0)
 eCells   = theSheet.Columns(0).QueryEmptyCells
 finRg    = eCells(eCells.Count - 1)
 If finRg.RangeAddress.EndRow<>theSheet.RangeAddress.endRow Then Exit Sub
 target   = finRg.GetCellByPosition(0, 0)
 theDoc.CurrentController.Select(target)
 End Sub

Dusty50 · March 30, 2021, 12:50am

<Sub LASTCELL()
theDoc = ThisComponent
theSheet = theDoc.Sheets(0)
eCells = theSheet.Columns(0).QueryEmptyCells
finRg = eCells(eCells.Count - 1)
If finRg.RangeAddress.EndRow<>theSheet.RangeAddress.endRow Then Exit Sub
target = finRg.GetCellByPosition(0, 0)
theDoc.CurrentController.Select(target)
End Sub>

Dusty50 · March 30, 2021, 12:55am

I give up…how to post the code so it comes across appropriately?
thanks

Ratslinger · March 30, 2021, 1:49am

Hello,

Edited your question code.

Use preformatted text icon in toolbar - upper left in question, comment, answer.

Just did so to your comment also.

Dusty50 · March 30, 2021, 2:17am

Perfect, but was wondering if there is some line/s of code that would do the same thing? Way back when, using that other sheet (MSFT) I recall some Auto_Open type language that did the same thing

Ratslinger · March 30, 2021, 2:20am

Your comment should be in reply to the answer not the question.

Ratslinger · March 30, 2021, 2:01am

Hello,

Just did a couple of quick tests. You can attach the macro to the Open Document event of the Calc file:

Get there from menu Tools->Customize. As the image shows, it is on the Events tab. Select the Open Document line, click on Macro... button and follow dialogs to get to your macro. Insure the Save in on the lower part of the Customize dialog is set to the Calc file and not LibreOffice.

Ratslinger · March 30, 2021, 2:23am

Nothing I know of as far as adding code to do this. Just consider, by adding code what makes it open a document? Something needs to trigger it. I believe you could execute the code from a command line but what does that achieve?

Lupp · March 30, 2021, 7:36am

I don’t think you can assign macros to the shown events per doucuments. The only task for what I use such assignments runs for all documents. Modifications regarding the document type or even the individual documant itself (via its URL? its content?) you need to implement inside the macro.
“onStartApplication” isn’t appropriate for the wanted action. The state of the document you need to run a macro like the posted one may not be reached before “onViewCreated”.

Ratslinger · March 30, 2021, 1:52pm

@Lupp,

Have used “Open Document” event many times over the years in most of the modules and did this on a per document basis. Before posting did test and still see no problem. This does execute only for this document. Have on some occasions (especially in Base) needed to use the “View created” event instead. Not certain why you noted “Start Application”. It is highlighted in the image but not used or referenced in the answer.

Dusty50 · March 30, 2021, 3:41pm

and thanks so much for the editing tutorial
Stay safe

Lupp · March 30, 2021, 3:42pm

The only case I use customized events currently is with “View created” and “Document has been saved as”. The induced action is the same for both events and is executed with any document of any origin loaded with LibO as far as I can see.
I should probably have kept silent… But this is interesting.
Which event settings are only relevant for specific documents, and in what way is the fact stored for/in these documents?

Ratslinger · March 30, 2021, 6:07pm

It is not a matter of which events, it is the Save In: on the bottom of the Customize dialog. If saved in the .ods it is only for that document and the other setting is “LibreOffice” which I have used rarely. With Base forms (Writer documents) these events, per document, I use regularly in performing specific tasks for opening a particular form. I set menus, initialize data and other functions. Have also used in Calc documents often.

Just to prove to myself I wasn’t dreaming, I took the same document I tested the answer with and changed the event from “Open Document” to “View created” and the opening of that document was the same with the macro functioning correctly. Then went to a different Calc document and opened that. There was no macro executed there. Don’t see how there could be since the macro is only in the first document. No matter where I save or which sheet, it always opened where it was saved. Not so when the macro was enabled in the other document.

Lupp · March 30, 2021, 6:34pm

Thanks @Ratslinger!
Simply:

Doubting: I probably never had a task for which I needed to call macros by the kind of events per document (no base user e.g.).
Doubting: I may have looked for the feature, but my eyes had a bad day. More likely the brain.
Surely: The UI designers are pranksters. They place the elements concerning similar functionality three and a half times near the top of a tabbed dialog, and the fifth time at the bottom.
Marx Brothers Prize? !

All the same: It was my duty to look at the facts before posting a wrong claim.
Old “Click on Start if you want to quit!” I should know meanwhile.