Producing a hyperlink button in Calc

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?

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("")
    button = doc.createInstance("")
    button.Name = "cmd_link"
    button.Label = selection.String
    button.TargetURL = selection.String
    button.ButtonType =
   	shape.Control = button
End Sub

image description

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. :slight_smile:

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? …

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()

	Call SetAnchorToCellResize(shape)
End Sub

Sub SetAnchorToCellResize(shape)
	Dim document As Object, dispatcher As Object

	document = ThisComponent.CurrentController.Frame
	dispatcher = createUnoService("")

	Dim args(0) As New
	args(0).Name = "SwitchControlDesignMode"

	args(0).Value = True
	dispatcher.executeDispatch(document _
	 , ".uno:SwitchControlDesignMode", "", 0, args()) 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("")
	shape.Control = button
	With shape
		If ResizeWithCell Then
			.Anchor = selection  'it's a cell object
			.ResizeWithCell = True
		End If
	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.

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).