[Solved] Macro works manually but not when hyperlinked

[JamesSmith32]

I have this Macro

Code: Select all

Sub ScrollToAvailableColumn( Optional iRowIndex )

Dim oDoc As Object      : oDoc    = ThisComponent
Dim sCell 
Dim oController
oController = oDoc.getCurrentController() 
Dim oSheet As Object    : oSheet  = oDoc.CurrentController.ActiveSheet
sCell = oSheet.getCellByPosition(2, 2)
oController.select(sCell)

    If IsMissing( iRowIndex ) Then iRowIndex = oDoc.CurrentSelection.RangeAddress.StartRow
    Dim oRow As Object   : oRow = oSheet.Rows.getByIndex( iRowIndex )
    Dim oRanges As Object   : oRanges = oRow.queryContentCells(_
    com.sun.star.sheet.CellFlags.VALUE OR _
    com.sun.star.sheet.CellFlags.DATETIME OR _
    com.sun.star.sheet.CellFlags.STRING OR _
    com.sun.star.sheet.CellFlags.FORMULA)
   
   	Dim lColumn As Long
    Dim lCount As Long      : lCount  = oRanges.getCount()
    If lCount > 0 Then        lColumn    = oRanges.getByIndex( lCount - 1 ).RangeAddress.EndColumn + 1
    Dim oCell As Object     : oCell   = oRow.getCellByPosition( lColumn + 1, 0 )
oController.select( oCell )

End Sub

When I run it manually it works fine, just as I want it to. However, when I run it with a hyperlink or a pushbutton it wont work.

Code: Select all

=HYPERLINK("vnd.sun.star.script:standard.scroll.scrolltoavailablecolumn?language=Basic&location=document"; "Click here to Scroll" )

How can I get this to work with Hyperlink?

Thanks

[JeJe]

Its easier for people to look at the problem if you post the document and give a more specific description than just it won't work.

if you add the msgbox line you can see that RangeAddress.StartRow returns a string of the hyperlink target macro when the hyperlink is clicked not a row number.

Code: Select all

  If IsMissing( iRowIndex ) Then iRowIndex = oDoc.CurrentSelection.RangeAddress.StartRow
    msgbox irowindex

[Villeroy]

Inspect your variables. iRowIndex is not a number nor missing. It is the URL of the calling hyperlink.

See download/file.php?id=27213

[JamesSmith32]

Yes, fixed, thanks both of you.

[arfgh]

around this, and hyperlinks... what i am unable to get to works is this hyperlink added from basic

Code: Select all

=HYPERLINK("vnd.sun.star.script:standard.scroll.scrolltoavailablecolumn?language=Basic&location=document"; "Click here to Scroll"

problem is i cant set the 'tooltip' as you did on that example ^^
some idea about ?