Hi.
Is there a simple way to set up a custom function in LibreOffice Calc to test if a cell contains a hyperlink? Sort of like IFHYPERLINK.
I would like a Formula that could work like
=IFHYPERLINK(A1,“Worksheet”,"")
Thanks!
Thanks for everything so far. For some reason, if the hyperlink is removed from A1, the cell with the formula =CELL_HAS_HYPERLINK(0;0) in it still shows TRUE. If I delete the formula and type it back in, it updates to FALSE.
Hello @Rabbit1,
EDIT 2017-08-24 - made usable for ActiveSheet
EDIT2 - updated method of getting the Sheet index, Thanks @PYS
Function Cell_has_Hyperlink( lColumnIndex&, lRowIndex&, Optional lSheetIndex& ) As Boolean
REM Returns <TRUE> if the specified cell contains a Hyperlink.
REM The parameter indexes for Column, Row, and Sheet, are all zero-based.
REM Quietly fails if the specified Sheet index does not exist.
REM To use the current Sheet, you can omit the <lSheetIndex> argument.
On Local Error GoTo Error_Exit
If IsMissing( lSheetIndex ) Then lSheetIndex = ThisComponent.CurrentController.ActiveSheet.RangeAddress.Sheet
Dim oSheets As Object, oCell As Object
oSheets = ThisComponent.getSheets()
oCell = oSheets.getCellByPosition( lColumnIndex, lRowIndex, lSheetIndex )
If HasUnoInterfaces( oCell, "com.sun.star.text.XTextFieldsSupplier" ) Then
Dim oTextFields as Object, oTextField As Object
oTextFields = oCell.getTextFields().createEnumeration()
While oTextFields.HasMoreElements
oTextField = oTextFields.NextElement
If oTextField.getPropertySetInfo().hasPropertyByName( "URL" ) Then REM found a URL TextField.
Cell_has_Hyperlink = True
Exit Function
End If
Wend
End If
Cell_has_Hyperlink = False
Exit Function
Error_Exit:
Cell_has_Hyperlink = False
' Msgbox "Error in CELL_Has_Hyperlink( " & lColumnIndex & "," & lRowIndex & "," & lSheetIndex & " )"'
End Function
If you copy-paste that macro into your Standard Basic Macro Library, then you could write a Formula in Calc cells:
=CELL_HAS_HYPERLINK(0;0) ( Returns TRUE if cell A1 from ActiveSheet contains a hyperlink ).
=CELL_HAS_HYPERLINK(0;0;0) ( Returns TRUE if cell A1 from Sheet1 contains a hyperlink ).
Thanks for your help. Librebel, is there a way to to use ActiveSheet instead of Sheet Index? I would like to be able to use this formula in multiple sheets without having to change the sheet index. Or am I not understanding something?
Hello @Rabbit1,
That would make the function much more useful indeed.
i updated the macro, now it supports ActiveSheet as well.
NB. the order of arguments has changed a little, plus a helper function getSheetIndex()
Hi @librebel my edit is only to add ' at the end of the commented Msgbox line (of course not needed in basic, but here for good formatting).
Regards
Merci @pierre-yves, it looks indeed much better that way
it seems that the color coding interprets everything after the apostrophe as a comment, including new lines, until it encounters another apostrophe…
By the way, the looping procedure on all the sheets is unnecessary. We have directly the sheet number in the property:
ThisComponent.CurrentController.ActiveSheet.rangeAddress.sheet
Regards
i should’ve looked there before…
thanks and regards, lib
i updated the method and removed the obsolete helper function getSheetIndex() .
Generally: Type “extract hyperlink” into the “Search or ask your question” control. There will be matches.
Function in calc to identify if a cell contains an hyperlink, also if it is behind the displayed text? (specifically if you aim at the HYPERLINK function.
You may simplify and use the code I posted in [Calc BASIC]Function to convert attributes and links to html (View topic) • Apache OpenOffice Community Forum.
Hope you get answers knowing simpler means.