Calc turn design mode on with a macro

[Bort]

I’m trying to figure out how I get a macro to turn design mode on, select the button and delete it, then turn design mode off. I found a macro script on here that creates the button. I want it to always place the button in E7.
When I tried adding the delete button code it would complete and delete the button but design mode remained on.

test2.ods

Of course this work better if I could figure out how to make a macro update the button link instead of doing all it’s got to do to delete the button that’s there and create a new one.
I got this far thanks to info on this ask.libre site.

[mauricio]

You can update any property in any control, for example:

Sub update_target_url()
	doc = ThisComponent
	form = doc.CurrentController.ActiveSheet.DrawPage.Forms(0)
	button = form.getByName("cmd_link")
	button.Label = "LibreOffice Ask"
	button.TargetURL = "https://ask.libreoffice.org"
End Sub

For switch design mode

frame = doc.CurrentController.Frame
dispatch = createUnoService("com.sun.star.frame.DispatchHelper") 
dispatch.executeDispatch(frame, ".uno:SwitchControlDesignMode", "", 0, Array() )

[Bort]

Thank you! I believe I’m close but I’m not doing something right. I updated the example file with your update_target_url macro. An edited version of it. I’m trying to get it to change the button URL in cell E7 (only).
And I’m only going to be using this to link other spreadsheets in the same document. The project spreadsheet has a lot of sheets, it might end up having 50 sheets I’m trying to provide an easy way to go directly to them instead of having to scroll through all of them with the navigation arrows at the bottom of calc.

[Bort]

Hooray! I got this working but it only works if I have the cell the button is in selected. If I have any other cell selected it doesn’t update the button to the new link when the listbox is changed. I (finally) edited code successfully and got it working. I edited it to this:

Sub update_target_url()
 doc = ThisComponent
    selection = doc.CurrentController.selection

    If selection.ImplementationName <> "ScCellObj" Then
        Exit Sub
    End If

    doc = ThisComponent
    form = doc.CurrentController.ActiveSheet.DrawPage.Forms(0)
    button = form.getByName("cmd_link")
    button.Label = "Go"
    button.TargetURL = selection.String
End Sub

How do I get it to always change the button without needing to have the cell the button is in selected? In the example file the button is in E7.

I tried changing the code to selection = "E$7"
That errored. I’m clueless when it comes to editing or writing this code. It baffles me.

[mauricio]

Please, look the wiki:

https://wiki.documentfoundation.org/Macros/Basic/Calc/Ranges#Ranges

[Bort]

Muchas Gracias!!! That’s extremely helpful in learning!
https://wiki.documentfoundation.org/Documentation