Ask Your Question

Is there a simple way to pass a push button parameter to a macro?

asked 2018-06-23 09:32:38 +0100

Eaglecat gravatar image

I have a very elaborate Calc spreadsheet that has an index sheet with a lot of buttons that are used to navigate to various other sheets. There are about 70 other sheets and most of these have buttons for navigating back to the main index sheet and also go to other related sheets.

Currently I use separate macros to navigate to each of the sheets that exist in the spreadsheet and I call them individually with relevant command buttons that call to each specific target sheet macro. I would like to streamline this voluminous & mostly repetitive code and only use only one macro subroutine where the relevant target sheet name would be passed via an object parameter of the calling command button - but I don't know how to accomplish this.

Such a solution would also make it much easier to edit sheets by simply copying relevant command buttons from other sheets and modifying the relevant command button properties target parameter as required.

I have seen How to start a macro with user-specified parameters? but the answer in method 3 and example spreadsheet are both too obtuse for me to understand how to apply that to my case. Can you please elaborate for my specific case where I want to pass the name of a variable target sheet (preferably as a string variable but a unique integer index would be OK too).

edit retag flag offensive close merge delete


Quoting @Eaglecat: "...the answer in method 3 and example spreadsheet are both too obtuse for me to understand how to apply that to my case."
I don't clearly understand the term "obtuse" in this context. However, the method I tried to explain in the other thread is not expected to be used where a much simpler way is open. In this special case the URL property of the form control does everything you need.

Lupp gravatar imageLupp ( 2018-06-23 17:09:13 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2018-06-23 13:45:05 +0100

pierre-yves samyn gravatar image


There is no need for a macro for that...

You can create your buttons by InsertHyperlink and choose the target.

image description

Since your buttons are already created, just modify them by entering their URL property with the name of the target sheet preceded by a #

You can even specify a cell, e.g. #Sheet2.B2

image description

See C:\fakepath\PushButton.ods

HTH - Regards

edit flag offensive delete link more


Thanks Pierre for taking the time and effort to give such a detailed reply. I am fairly new to Calc. You have answered my question with a far better solution 😀

Eaglecat gravatar imageEaglecat ( 2018-06-23 14:37:15 +0100 )edit

Please delete this answer and add a comment instead. See guidelines for asking.

Jim K gravatar imageJim K ( 2018-06-23 17:57:39 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-06-23 09:32:38 +0100

Seen: 127 times

Last updated: Jun 23 '18