Ask Your Question

Identify the button clicked on a calc worksheet

asked 2019-01-18 15:16:32 +0200

rebelxt gravatar image

Given a calc sheet with several buttons that execute the same macro when clicked, is it possible for the macro to determine which button was clicked? Maybe by accessing the button properties to read the button name or additional information property. Note that the buttons are not in a dialog, but are defined on a sheet.

Of course it is possible for each button to execute a unique macro that calls a common subroutine. Being able to identify the button would allow simpler code when there are many buttons.

My current release is 6.0.6, but I could upgrade to a more recent release if necessary. My OS is Linux Mint 18.3.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2019-01-18 20:52:49 +0200

Lupp gravatar image

updated 2019-01-18 23:20:28 +0200

Specifically in Calc it can be a good idea to have a dedicated cell range somewhere which is used to pass someting like parameters to the Sub, and probably to branch the Sub depending on the values. The respective cells can even provide pre-calculated values depending on any content of the sheets. The Sub then only needs to know the range.

A means usable with any Form control is the control's .Tag property. Sometimes I used it regarding the URL-query syntax to pass a string to the called Sub which can be evaluated by the respective Sub itself or a helper sub. If I want to tell the called routine in what cell range to find more details I might enter
Direct parameters can also be passed this way: ?days1=even&startingtime=13h15min&maximum=123456

The interpreting Sub only needs to check for the "?" (based on a first split probably), then split the final part by "&", and evey subpart then by "=" to get what it wants to know. The .Tag part in front of the "?" can be used then for another purpose.

In a similar way, just using a slightly adapted syntax, I sometimes passed parameters to an interpreting Sub by the name of a CellStyle when the Sub was called as the handler for the 'Content changed' event of a sheet.

===Edit1 2019-01-23:20 CET=== The .Tag property of a FormControl has a misleading name like "Additional information" in the object editor. From the code it is accessed as myControlObject.Tag anyway.

edit flag offensive delete link more


As far as I can tell, the .Tag property is not available in my application (see my answer below).

rebelxt gravatar imagerebelxt ( 2019-01-19 00:04:58 +0200 )edit

answered 2019-01-18 20:33:39 +0200

rebelxt gravatar image

updated 2019-01-18 22:12:03 +0200

I had an epiphany of sorts, and inserted an argument into the Sub statement. That argument refers to an object containing enough information to satisfy my requirement.

Edit: The pertinent macro statements for me are:

Sub ButtonClicked (ref As Object)
text = ref.Source.AccessibleContext.Text
End Sub

Set an appropriate breakpoint and a watch on ref, then click the button; you can then see what other parameters than Text are available. Additional code is necessary to create a useful macro. Different requirements make it impossible to be specific about what is useful in other situatiions.

This is for buttons on a calc sheet, not buttons in a dialog. The macro must be connected to the button in the Events tab of the button properties dialog; see LibreOffice help for instructions.

edit flag offensive delete link more


Quoting @rebelxt: "That argument refers to an object containing enough information to satisfy my requirement."
What kind of object did you use. Be so kind to complete your answer.

Lupp gravatar imageLupp ( 2019-01-18 20:49:21 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-01-18 15:16:32 +0200

Seen: 51 times

Last updated: Jan 18