Ask Your Question

Macro recording limitation in Calc

asked 2017-06-13 17:44:14 +0200

cliffcoggin gravatar image

Good Day folks. As a recent migrant from Windows to Linux I need to revise my spreadsheet macros. Mostly I have succeeded, except for when a dialogue box is involved, at which point the macro stops and waits for me to make a selection. Two examples: [1] when trying to print with a macro it does not remember the number of copies I had set, so I have to enter the number manually; [2] when trying to set protection the macro does not remember "cells visible to all users" setting, so again I have to enter that setting by hand. Is there a graphical way round this problem? Is there a more suitable version of Libreoffice? I have Libreoffice version

edit retag flag offensive close merge delete


Good day @cliffcoggin, please post the macro code here so we can see where it goes wrong.

librebel gravatar imagelibrebel ( 2017-06-14 04:50:16 +0200 )edit

I tried to paste the code but it exceeds the maximum length permitted here, nor can I see how to add it as an attachment, Should I put it in the answer box? Cliff.

cliffcoggin gravatar imagecliffcoggin ( 2017-06-14 10:05:28 +0200 )edit

you could paste the code in a site such as pastebin, then post the resulting URL here.

librebel gravatar imagelibrebel ( 2017-06-14 10:08:40 +0200 )edit

Not sure i have done it correctly, but try this:

cliffcoggin gravatar imagecliffcoggin ( 2017-06-14 10:53:28 +0200 )edit

it seems the purpose of the macro "CLAtoA11" is to copy cells A5:E5 of sheet 4 into cells A11:E11 of sheet 1. There is no need to write a macro for this purpose, let alone record one. Instead you could just write a formula directly into cells A11:E11 of sheet 1, which retrieves the values from cells A5:E5 of sheet 4. E.g. in cell A11 of sheet 1 you type the formula "=Sheet4.A5", in cell B11 of sheet 1 you type the formula "=Sheet4.B5", etc.

librebel gravatar imagelibrebel ( 2017-06-14 12:39:38 +0200 )edit

That's part of the macro, but it also starts by unprotecting the sheet before doing the copy operation, then protecting afterwards. It's the protection step that stops the macro running. I realise i could do the operation manually but that could be said of any macro. The use of a formula is not a viable option for me because the sheet is a template that is renamed and saved in many other files, only some of which do I want the copy operation to be done.

cliffcoggin gravatar imagecliffcoggin ( 2017-06-14 13:03:49 +0200 )edit

In addition to the above I read in the help notes that dialogue boxes interfere with macros. That I believe is the heart of the problem and what i am hoping to get around.

cliffcoggin gravatar imagecliffcoggin ( 2017-06-14 13:06:03 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2017-06-15 01:29:48 +0200

librebel gravatar image

updated 2017-06-15 03:00:10 +0200

Helo again @cliffcoggin, in that case i would recommend a macro using API calls instead of dispatch commands, e.g.:

Sub CLAtoA11( )
    Dim oSheet1, oSheet4, oSource, oTarget
    oSheet1 = ThisComponent.Sheets.getByIndex( 0 )
    oSheet4 = ThisComponent.Sheets.getByIndex( 3 )
    oSource = oSheet4.getCellRangebyName( "A5:E5" )
    oTarget = oSheet1.getCellRangebyName( "A11" )
    oSheet1.copyRange( oTarget.CellAddress, oSource.RangeAddress )
End Sub

NB. this bypasses the Protection dialog altogether.

edit flag offensive delete link more


Many thanks for that. I shall experiment further after what reading API calls are.

cliffcoggin gravatar imagecliffcoggin ( 2017-06-15 17:07:14 +0200 )edit

You're welcome, plz see the LibreOffice API Reference.

librebel gravatar imagelibrebel ( 2017-06-15 17:52:20 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-06-13 17:44:14 +0200

Seen: 191 times

Last updated: Jun 15 '17