I have a button, which executes a macro.
Within this macro I check cells on the sheet, and would like to add a button on each row that passes the check. I have tried
Dim oBut
oBut = createobject(“com.sun.star.form.control.CommandButton”)
which says the module cannot be loaded.
This button should execute another macro.
I have been unable to find any examples on the net, and realise I am most likely misunderstanding pretty much everything.
Any pointers would be greatly appreciated.
Welcome!
And if the macro successfully inserts a button and the data in the cell will be changed so that the row will no longer pass the test - will you try to remove the erroneous button? Are there too many difficulties?
Perhaps the script can be simplified? For example, with a macro to process a double-click on a cell - if the row passes the test, then continue the script execution, otherwise it tells the user in detail what errors were found in the row…
My plan is the initial button (‘Reset’) executes the main macro, which looks at the sheet and inserts the other buttons where appropriate. No matter if the sheet changes at all these new buttons remain.
When these new buttons are clicked they should execute another macro that does work on its row and then deletes the button that was clicked, every new button should only be able to be clicked once (hence why I want to delete it).
I want to be able to save the state, and then once a day (at random times) click ‘Reset’ which restores the new buttons where appropriate.
Come to think of it, they do not have to be buttons, maybe just text in the cell that executes a macro when clicked. I am open to further simplification ideas.
Thanks JohnSun, your suggestion to simplify was indeed good.
I have dropped buttons, and used the sheet double click event to do exactly what I wanted with text strings instead.
I like it, in fact I’ll implement your hyperlink idea, thanks so much for your effort and help!
Another option may be to add listeners and a single set of buttons.
When a cell selections changes the buttons can be updated to work on that cell or row.
See the Build Form example for an example adding buttons.
See the Calc Select Listener and Calc Modify Listener.
By the way the examples listed here can be run in a Codespace directly in your web browser.
I just want to point out to anyone here for similar tasks, I ended up recording a macro while manually adding a button and editing its properties, then deleting it.
While this did not work it did give me much better terms to search for, and led me to figure out how to do it.
I guess knowing where to start can be the hardest step.
Sub CreateButton()
col = 7
row = 20
oSheet = ThisComponent.currentcontroller.activesheet
oForm = oSheet.DrawPage.getForms().getByIndex(0)
oCell = oSheet.getCellByPosition(col, row)
'Create the button
oBut = CreateUnoService("com.sun.star.form.component.CommandButton")
oBut.Name = "But_" + col + "_" + row
oBut.Label = "RECV"
'Create the shape to contain it
oShape = ThisComponent.createInstance("com.sun.star.drawing.ControlShape")
oShape.Name = "CS_" & oBut.Name
'Add the shape to the page, set anchor and position, size
oShape.setControl(oBut)
oSheet.DrawPage.add(oShape)
oShape.Anchor = oCell
oShape.setSize(oCell.Size)
oShape.setPosition(oCell.Position)
'Add the event to handle clicks
aEvent = CreateUnoStruct("com.sun.star.script.ScriptEventDescriptor")
With aEvent
.AddListenerParam = ""
.EventMethod = "actionPerformed"
.ListenerType = "XActionListener"
.ScriptCode = "vnd.sun.star.script:Standard.Module1.ButtonClick?language=Basic&location=document"
.ScriptType = "Script"
End With
oForm.registerScriptEvent(oForm.Count-1, aEvent)
End Sub
Sub ButtonClick(oEvent as com.sun.star.awt.ActionEvent)
oSheet = ThisComponent.currentcontroller.activesheet
oForm = oSheet.DrawPage.getForms().getByIndex(0)
'Remove the event (not sure if this is required, but seems like it should be)
For i = 0 To oForm.count - 1
oFormEle = oForm.getbyindex(i)
If (oFormEle.Name = sButName) Then
oForm.revokeScriptEvent(i, "XActionListener", "actionPerformed", "")
Exit For
End If
Next i
'Find which button was clicked, perform some work then delete it
For i = 0 To oSheet.DrawPage.count - 1
oShape = oSheet.DrawPage.getbyindex(i)
If (oShape.Name = "CS_" & oEvent.Source.Model.Name) Then
oCell = oShape.Anchor.CellAddress
Print "Button clicked on Sheet " & oCell.Sheet & ", Col " & oCell.Column & ", Row " & oCell.Row
oShape.Dispose
Exit For
End If
Next i
End Sub