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 5.1.6.2
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.
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.
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.
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.
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 )
oSheet1.unprotect("")
oSource = oSheet4.getCellRangebyName( "A5:E5" )
oTarget = oSheet1.getCellRangebyName( "A11" )
oSheet1.copyRange( oTarget.CellAddress, oSource.RangeAddress )
oSheet1.protect("")
End Sub
NB. this bypasses the Protection dialog altogether.
Many thanks for that. I shall experiment further after what reading API calls are.