Ask Your Question

How to determine the row where the macro button was clicked?

asked 2018-05-18 00:10:00 +0200

Mirador gravatar image

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?

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2018-05-18 01:06:43 +0200

Jim K gravatar image

updated 2018-05-18 01:43:53 +0200

Create the button for each row using HYPERLINK, then format the hyperlink to look more or less like a button.

=HYPERLINK(""&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

Other solutions:

edit flag offensive delete link more


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.

Mirador gravatar imageMirador ( 2018-05-18 21:05:31 +0200 )edit

In that case, change the URI to location=document. For more details, see

Jim K gravatar imageJim K ( 2018-05-19 17:36:32 +0200 )edit

answered 2018-05-18 02:44:09 +0200

Lupp gravatar image

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
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 @Jim K .

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-05-18 00:10:00 +0200

Seen: 490 times

Last updated: May 18 '18