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

[Eaglecat]

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).

[Lupp]

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.

[PYS]

Hi

There is no need for a macro for that…

You can create your buttons by Insert▸ Hyperlink and choose the target.

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

See PushButton.ods

HTH - Regards

[Eaglecat]

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

[jimk]

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