Get the label of the button that was just pressed to call a macro

[jmcsys]

I have a libre calc spread sheet where each cell in column A has a button. Currently the buttons are anchored to their respective cells. When the button is pressed it runs a macro that uses data from the Cell to the right of it.

Ex: Button in cell A1, when pressed it calls a macro and uses the text in cell B1.

Ex: Button in cell A2, when pressed it calls a macro and uses the text in cell B2.

Both above examples are working now, but I have to hard code the label of each button in my code for my macros to properly reference which button was pressed.

I can see me expanding this to dozens of rows if not hundreds. Is there a way I can make a “Generic” button with no label and anchor it to a cell say A3, and when I press it I can then find out which cell the button is anchored to without knowing anything else about the button? Like a function that returns the last used button. i.e. ThisButton or something. I don’t know how to drill down from ThisComponent to get the info I seek but I assume this is the way to go.

If this was to work I would then want to copy the button in cell A3 and paste a new copy into cell A4. This new button in A4 when pressed would know to use text in B4 when pressed.

Greatly appreciate the help!

[Ratslinger]

Hello,

Seems to me you don’t need any buttons at all.

The macro can be attached to a toolbar item. The value to be used would come from the selected cell. Select wanted cell & press the Toolbar item and use that value in the macro:

Sub UseSelected
    Dim oCurrentelection as Object
    Dim sString              as String
    Dim nValue              as Double
    oCurrentSelection = ThisComponent.getCurrentSelection()
  Rem selection depending upon what you may be doing
    sString = oCurrentSelection.getString()
    nValue = oCurrentSelection.getValue()
    Print sString
    Print nValue
End Sub

Edit:

This is not a copy/paste as you asked for. My comment explained the reason. Each control must have a unique internal name and must be anchored to the cell immediately left of the data on the same line:

Sub UseSelected(oEvent)
    Dim oSheet             as Object
    Dim oDrawPage          as Object
    Dim oItem              as Object
    Dim oControl           as Object
    Dim oCellAddress       as Object
    Dim oValueCell         as Object
    Dim sName              as String
    Dim sControlName       as String
    Dim nCount             as Integer
    Dim x                  as Integer
    Dim nColumn            as Double
    Dim nRow               as Double
  Rem - Get control name
    sName = oEvent.Source.Model.Name
    oSheet=ThisComponent.CurrentController.ActiveSheet
    oDrawPage = oSheet.getDrawPage()
    nCount = oDrawPage.getCount()
  Rem - Search for control name on draw page
    For x = 0 to nCount-1
        oItem = oDrawPage.getByIndex(x)
        oControl = oItem.getControl()
        sControlName = oControl.Name
        If sName = sControlName Then exit For       Rem Control found
    Next x
  Rem - Get anchored cell address
    oCellAddress = oItem.Anchor.getCellAddress()
    nColumn = oCellAddress.Column
    nRow = oCellAddress.Row
  Rem - Get data from cell one column to right
    oValueCell = oSheet.getCellByPosition(nColumn+1, nRow)
    Print oValueCell.String
End Sub

[jmcsys]

@Ratslinger Thanks for this suggestion. While this is an option, its not my ideal option. As the user would have to select a cell and then click a button on the toolbar. I am hoping to just have the user make one click and it runs the macro, and not have the user worry about which cell is currently selected.

[Ratslinger]

The other suggestion requires each button to be unique in some way - either label or internal name. A button may be anchored to a cell but knowing this cell is only available on Draw Page.

So in the macro, using oEvent you could obtain the name of the button, then search the draw page for that control, obtain the anchor, get the cell address and use that to access the Column +1 and Row for the data.

Another alternative is to use the same macro in the answer attached to a sheet event. Right click the Sheet tab & select Sheet events. Now attach the macro to a double or right click event. Still no buttons and now only one place to click.

[jmcsys]

I am quite new to libre basic. How would I use the oEvent to get the name of the button? If you don’t mind a simple example it would be greatly appreciated!

Using the Sheet events and a right click might do the trick. I think I can get that to work so I will try it out.

[jmcsys]

The Sheet events tied to a double click is so far the best method and easiest to maintain. The right click event is not as good as it can leave another cell selected which is different then the cell you are right clicking on.

I still would like to explore my original option but your suggestion is certainly working well!

[jmcsys]

Thanks! It works like I wanted and I can have the double click sheet event as a bonus!

Is the oEvent automatically passed to all macros? Are other objects also passed? Do you have a good source for more info on these objects? Thanks again!

[Ratslinger]

@jmcsys Looks like third time is a charm. Glad it is what you wanted.

Asked:

Is the oEvent automatically passed to all macros?

Only those triggered by an event. Seems you may want more information. Please see this post → To learn LibreOffice Base are there introductions or tutorials?

Make sure to click on ‘more’ at the end of the answer. If you haven’t gotten it already, MRI is a real plus for examining properties, methods, interfaces and services. There are also a number of links to documents covering macros.