Macros and form controls

[Lucca]

I have an issue with LibreOffice Calc version 24.8 Dutch macros and form controls.

In a Calc document with seven worksheets, I entered a macro using the ‘recording macro’ function in worksheet three. This macro is saved in the macro editor under the document’s macro library, labeled ‘buttonNAMEWORKSHEET3’. A button is created in worksheet three, and under ‘properties of controls’ within ‘events/perform action’, the macro is linked to this button. After testing, the macro functions as intended.

However, when I include another macro in worksheet four and test it through the macro editor, it works successfully. I then create a new button in worksheet four and link the new macro to the new button named ‘buttonWORKSHEET4’. Now, when I activate the button in worksheet three, it executes the instructions from the macro created for worksheet four, using the data from worksheet four.

What could be causing this issue? Could it be related to the event type in the ‘properties of controls’?

[JohnSUN]

No, I am more than sure that the reason for this behavior is in the macro code, not in the way they are called. More precisely, it will be possible to determine the cause of the incorrect behavior only after familiarizing yourself with the code that the macro recorder created for you. Did you record macros using only the keyboard and menu commands? Or did you sometimes click the mouse?

[Lucca]

Thanks JohnSUN
I have no knowledge of BASIC code, neither with programming at a whole. That is the reason why I’ve attempted making a macro with the ‘macro recorder’. However to familiarise myself with the code, that’s hard. I’ve recorded mouse-driven.

[JohnSUN]

Just show us your spreadsheet (attach it to your question or to another comment
) and we might be able to suggest a more robust way to accomplish your tasks.

[Lucca]

Basis.ods (47.6 KB)

[JohnSUN]

As far as I understand, your problem is related to the macros knopSTAGE.Macrostage and knopGC.MacroGC? Both of them should copy the contents of 249 cells from the range D252:D500 to the adjacent column E on the sheet where the button was pressed?

I couldn’t get them to work either - both copy the wrong thing and not to the desired location.
Let’s do this task as described in KopieerBereik?
Let’s take advantage of the fact that you don’t need to search or somehow determine the sheet on which the button was pressed - if the macro was launched by pressing the button, then it is on the ACTIVE sheet (it is difficult to press a button on some other sheet).
So you only need to get the current sheet - ThisComponent.getCurrentController().getActiveSheet()

Now get ranges by their names - oSheet.getCellRangeByName("D252:D500")

And use the technique described in KopieerBereik - ...setDataArray(...getDataArray())

The macro will be very short, something like this:

Sub CopyD252_D500ToE()
Dim oSheet As Variant 
Dim oSourceRange As Variant 
Dim oTargetRange As Variant 
	oSheet = ThisComponent.getCurrentController().getActiveSheet()
	oSourceRange = oSheet.getCellRangeByName("D252:D500")
	oTargetRange = oSheet.getCellRangeByName("E252:E500")
	oTargetRange.setDataArray(oSourceRange.getDataArray())
End Sub

Feel free to assign it to both buttons, on the third sheet and on the fourth sheet - it should work correctly.

[Lucca]

Thanks a lot.
There is a thing however. In D there are formulas. The aim is to paste only the results.value to E and the column or cells in E should move to the right while pasting.

[JohnSUN]

@Lucca

In D there are formulas. The aim is to paste only the results

This is not a problem - the .getDataArray() method will take the calculated values ​​from the cells, not the formulas, and the .setDataArray() method will insert them into the cells.

@Lucca

and the column or cells in E should move to the right

Oh yeah, I didn’t notice args5 “MoveMode”= 1 in your macro. Yes, before copying the values, you need to insert empty cells in column E, shifting everything else to the right. This can be done in one line:
oSheet.insertCells(oTargetRange.getRangeAddress(), com.sun.star.sheet.CellInsertMode.RIGHT)
Just insert this line after defining the oTargetRange range and before copying the values, and then remind the macro which range should be the target (since the original target range will shift one column)

Sub CopyD252_D500ToE()
Dim oSheet As Variant 
Dim oSourceRange As Variant 
Dim oTargetRange As Variant 
	oSheet = ThisComponent.getCurrentController().getActiveSheet()
	oSourceRange = oSheet.getCellRangeByName("D252:D500")
	oTargetRange = oSheet.getCellRangeByName("E252:E500")
	oSheet.insertCells(oTargetRange.getRangeAddress(), com.sun.star.sheet.CellInsertMode.RIGHT)
	oTargetRange = oSheet.getCellRangeByName("E252:E500")
	oTargetRange.setDataArray(oSourceRange.getDataArray())
End Sub

[Lucca]

@JohnSUN

Sub CopyD252_D500ToE()
Dim oSheet As Variant 
Dim oSourceRange As Variant 
Dim oTargetRange As Variant 
	oSheet = ThisComponent.getCurrentController().getActiveSheet()
	oSourceRange = oSheet.getCellRangeByName("D252:D500")
	oTargetRange = oSheet.getCellRangeByName("E252:E500")
	oSheet.insertCells(oTargetRange.getRangeAddress(), com.sun.star.sheet.CellInsertMode.RIGHT)
	oTargetRange = oSheet.getCellRangeByName("E252:E500")
	oTargetRange.setDataArray(oSourceRange.getDataArray())
End Sub

Thanks so much JohnSUN, you made my day a JohnSUNNY day! Thanks, I would like to have the knowledge you have in mastering the Libreoffice Basic. Have a great day.

[JohnSUN]

I know what the Netherlands does for Ukraine - they do it despite the abundance of their own problems. My help is only a manifestation of my gratitude to everyone who uses Dutch.

[Lucca]

This is so, so beautiful JohnSUN ! Such a heartwarming message you are giving, a pearl shining through the darkness of war ! I wish to you and for all Ukrainian people that wisdom can soon reign and that mankind will soon realise how stupid and devastating it is to fight a war… May the dove of peace soon arrive on your land!