I have a sheet where I’m constructing a search URL.
While I can easily create a single hyperlink button I can’t find a function where I could do this automatically.
Alternatively is is possible to take a column of cells containing the constructed URL and convert to a button?
Thanks
Why a button? Use =HYPERLINK(<cell_containing_constructed_URL>) and use CTRL+Click to open the URL (Creating buttons from cell content would require user code)
I would like to use a button for users to open hyperlinks as well. Text links are ugly and not everyone places their cursor over an image to notice they’re hyperlinks. A button stands out and can plainly say “Click to Open Link”. That’s why people might want to use a button.
There isn’t such a “function” (tool?). To create one by yourself will require some user code.
Your “hyperlink button” surely is a PushButton used as a kind of sensitive area for a hyperlink?
Did you consider the fact that such objects basically are FormControl objects hosted by specialized shapes? The shapes are directly put into the DrawPage of the respective sheet The FormControl objects are put into a Form which in turn is contained in a Forms object which in turn is a member of the DrawPage again?
Simple? I would rather call it a lot of overhead. How shall the “hypelink buttons” be kept apart from actual form elements? Separate form? Specifivc naming?
A spreadsheet has a DrawPage and by that a Forms container per sheet. How to handle that if your composed URLs are in one sheet, biut the related.buttons shall be placed in different sheets?
You should probably not expect any contributor to develop the code for you.
IMO: Omit buttons for the purpose.
For add button in current cell with current link:
Sub create_button_link()
doc = ThisComponent
selection = doc.CurrentController.selection
If selection.ImplementationName <> "ScCellObj" Then
Exit Sub
End If
sheet = selection.Spreadsheet
shape = doc.createInstance("com.sun.star.drawing.ControlShape")
shape.setSize(selection.Size)
shape.setPosition(selection.Position)
button = doc.createInstance("com.sun.star.form.component.CommandButton")
button.Name = "cmd_link"
button.Label = selection.String
button.TargetURL = selection.String
button.ButtonType = com.sun.star.form.FormButtonType.URL
shape.Control = button
sheet.DrawPage.add(shape)
End Sub
wow…very advanced…but wait: one simple =HYPERLINK("…") -formula does the job also
Because there are different needs… and it’s more fun with macros. 
I did not see any different need, and if want it to do just for fun than do it for yourself…
@karolus: Thanks for clear commandments. We should chisel them in stone.
I confess, I also felt tempted to take the opportunity to study the creation of FormControl objects (hosted by shapes) from scratch, and even to set them to cell size, to make them resize with the cell… and all that nonsense despite the fact that there is no different need. Thanks again for saving my time.
Wait. Aren’t there cases where HYPERLINK() is a bit poor? …
@nombre
if you need changes in the code I can help you, don’t listen to those who don’t see other alternatives.
I actually developed code for the purpose, but didn’t publish it yet. I considered some questions coming up from the context: What Form to use, how to handle anchoring, size and position, feasability by function…
The core, of course, is very similar to what @mauricio posted. Probably I will come back to the topic (but not today).
It remains only to anchor the button to cell (resize with cell).
Well, I thought a different way. …
Sub create_button_link()
<...>
sheet.DrawPage.add(shape)
Call SetAnchorToCellResize(shape)
End Sub
Sub SetAnchorToCellResize(shape)
Dim document As Object, dispatcher As Object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
Dim args(0) As New com.sun.star.beans.PropertyValue
args(0).Name = "SwitchControlDesignMode"
args(0).Value = True
dispatcher.executeDispatch(document _
, ".uno:SwitchControlDesignMode", "", 0, args())
ThisComponent.CurrentController.select shape
dispatcher.executeDispatch(document _
, ".uno:SetAnchorToCellResize", "", 0, Array())
args(0).Value = False
dispatcher.executeDispatch(document _
, ".uno:SwitchControlDesignMode", "", 0, args())
End Sub
Is there a way to anchor a button to a cell without a dispatcher?
The button is hosted by the shape which has the properties .Anchor and .ResizeWithCell.
The anchor needs to be a single cell or a complete spreadsheet.
What I considered in addition was to create an extra Form for the “simple” URL buttons to get them separated from probably also present DataBase usage. It can be done creating the needed Form instance and assigning it to the property .Parent of every Control needing to be inserted into it.
At least it worked for me this way.
Anchoring a button to a cell
Call SetAnchorToCellResize(shape)
Sub create_button_link(Optional ResizeWithCell)
If IsMissing(ResizeWithCell) Then ResizeWithCell = True
<...>
shape = doc.createInstance("com.sun.star.drawing.ControlShape")
shape.Control = button
sheet.DrawPage.add(shape)
With shape
If ResizeWithCell Then
.Anchor = selection 'it's a cell object
.ResizeWithCell = True
End If
.setSize(selection.Size)
.setPosition(selection.Position)
End With
The setSize and setPosition lines should follow after the anchor is installed. And the button must be added to the sheet before. Very good!
@Lupp, thanks.
Upd
After opening the file, the width of the button is lost.
Снимок экрана от 2021-07-11 21-16-26.png
Not very good.
Seems there is a conflict. Please check my suspicion:
On the one hand the control must be assigned to the shape before it can accept specific settings.
On the other hand it has a hidden (only known to the object editor) memory of the size when it was first assigned, and subsequent resizing of the shape doesn’t update that. Under unclear conditions the hidden size overrides the size of the shape.
Reassigning the settings after everything is done by
nextButton = nextShape.Control
nextShape.Control = nextButton
makes the issue vanish.
Forget it!
After a while I couldn’t reproduce the issue at all (V 7.1.5).