I have a spreadsheet with multiple rows and a button per row. Is it possible to either have the cell where the button is located in get focus so that the address of the current cell can then be derived, or to somehow get the row that the macro button is located in, so that the macro action can be applied to the data in that row?
Create the button for each row using HYPERLINK, then format the hyperlink to look more or less like a button.
=HYPERLINK("vnd.sun.star.script:Standard.Module1.macro_action?language=Basic&location=application&ROW="&ROW(),"[Do Action]")
The macro can retrieve the row from the URL which is passed as a string parameter.
A complete example and more details are in my answer at Calc - Macro to change date and list order - #2 by jimk.
Other solutions:
- The simplest approach for a small number of buttons is to name the button with the row where it is located, for example “theButtonInRow4”. See OpenOffice Calc macro in Python - macro triggered by a click of a button, how to get a name of that button? - Super User.
- If each button has a unique name and is anchored to a cell, then the macro can find it and get the address from the anchor. See [Solved] Get reference of cell under button (View topic) • Apache OpenOffice Community Forum.
It seems that =HYPERLINK does not run the macro. I it stored with the Calc file rather than in My Macros, so maybe the path needs to be different, but I cannot work out what it should be. When a hyperlink is clicked the cell does not get focus so one cannot be sure that the current cell is in the row one wants. I guess using a parameter is one way of solving that but one which i was trying to avoid as it is a bit cumbersome, but if I can get hyperlink to run the macro then this might suffice.
In that case, change the URI to location=document. For more details, see Scripting Framework URI Specification - Apache OpenOffice Wiki.
Buttons are form controls and mainly made for forms, these mostly used in Writer …
For a button in a spreadsheet The simplest way I know to get at least the sheet number is to extract it by Parsing the value of
chosenParameterNameForTheEvent.Source.AccessibleContext.AccessibleParent.ExtendedAttributes
Have fun!
Well, a specific button won’t wander around. When you shape your sheets simply enter whatever you need into the row ‘Additional imformation’ on the ‘General’ tab of the button editor. (No idea for what reason it not is named ‘Tag’ there.)
Your code can then read it from chosenParameterNameForTheEvent.Source.Model.Tag, and if the text there is “Sheet1.F15” you may trust the author, and you ned not to trust in any naming rules for buttons.
If you think to need lots of buttons: Try the HYPERLINK surrogate suggested by @jimk .