VB to make a Function() ** ANSWER Pending for: SCROLL ROW type hyperlink i will

Question

VB to make a Function() ** ANSWER Pending for: SCROLL ROW type hyperlink

i will check back in a few days, when have time to enter a working example at the bottom of this top post.

UPDATE: note, the work that this will be used for is intense, for the amount of records processed etc.

i cannot do double click and accomplish the task.

if relevent: the option to edit records "within the cell" on my excel has been turned off (as a safety measure, cursor jumping around corrupting other cells etc). therefore double clicking danger to my cells does not exist. thanks.

-----

hi, i am needing to create a function in VB that is similar to =Hyperlink()

i have seen the use of FUNCTION() but not sure how to start.

am willing to make this item in pieces at a time, in case it is more complex than a normal request. (maybe to at least get started with example at bottom). thanks.

would like the command to scroll to the desired row, putting the destination 1 line below the header.

the way i would set up a scroll hyperlink, might call it a QUICK LINK:

=QLINK($AB$499; 1, $AF$499,"friendly name")     'can we use a semi colon just right of the destination, to help isolate the ROW for use in other VB splits to id ROW destinations, later.

or:

=QLINK(destination row & column default; row from header offset, move cursor to column in same row option, "friendly name")  

- destination cell may have a name testing for, in other examples, re: reason for move cursor option

----------

the code that already works for a similiar result (PROBLEM: not as viable for many Hyperlinks, for problems obtaining the row destination in vb SPLITS - additional work cells etc..) / Hyperlink, is:

sub ScrollRow()

    Dim J8 As String
    J8 = RANGE("J8")

        ActiveWindow.ScrollRow = RANGE(RANGE(J8).OFFSET(0, 0)).Row - 1 '(row, col)    'WORKS

        Application.Goto RANGE(RANGE(J8).OFFSET(0, 0))    'WORKS put cursor on destination

end sub

note: J8 is a work cell that allows a dynamic reference showing eg: BN17:BN17

J8 has: =SUBSTITUTE(CELL("address",$BN$17),"$","")&":"&SUBSTITUTE(CELL("address",$BN$17),"$","")

examples for problem: (my idea for a new Hyperlink)

=QLINK($AB499; 1, 0,"friendly name")    '0: move cursor to default col $AB

=QLINK($AB499; 1, 1,"friendly name")    '1: move cursor to home, same as: SendKeys ("{HOME}")

=QLINK($AB499; 1, $AF$499,"friendly name")    'move cursor to a different column, example have:

      Me.Cells(Target.Row, RANGE(L2).Column).Select    'workcell L2 shows: CW:CW

=QLINK(IF(ROW($A587)>=ROW($A$499),$AB$499,$AB$200),1,0;IF(ROW($A587)>=ROW($A$499),"T2","T1"))

==========

>> PROBLEM NEED HELP WITH:    >> FIX THIS:

Function QLink(destination; offset rows, cursor destination, friendly name)

ActiveWindow.ScrollRow = RANGE(RANGE(J8).OFFSET(0, 0)).Row '(row, col) destination eg

    'remove this eg's ref to J8..

    'please add to assume, IF(ISNA errors to a range = true, then NOTHING, within the code

    'add for offset rows

    'add for cursor destination, to different column in same row

    'add for friendly name

end function

'Please advise if best idea: to use a semi-colon to isolate 'Destination' ROW for future reference in vb.

- even if row is directly referenced, think there would be a command that would allow the test of a cell containing eg: =QLink() for its row, for a faster - easier reference of the ROW.. ?? if relevent:

    ROW(QLink, currentrow.selection..

or if have to make something up:

    GETROW(QLink,

---------

other examples are find the max number in a column. if possible, would like test if range = nothing / errors.. to include:    assume IF(ISNA = true then "" or nothing

guessing this will just be a line of code added to the FUNCTION(()) vb.

for a sample of that type of hyperlink including IF(ISNA .... click on my profile / user name: davexx.

see COL MIN-MAX hyperlink example at bottom. thanks in advance.

Explanation / Answer

From what I understand Dave needs, I would be tempted to use a BeforeDoubleClick worksheet event to do this. When a cell is double-clicked, the event code would analyse the contents of the double-clicked cell and select cells and scroll as required. If the cell does not contain a specific keyword then no action would be taken. To identify the cells that are going to work as hyperlinks, you could use a function that simply returns a friendly name to the cell, but contains a reference to the target cell.

Something like this in a normal code module:

Function QLink(rngToSel As Range, strFriendlyText As String)
    QLink = strFriendlyText
End Function


And something like this in a sheet module:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim strFormula As String, strRefToSel As String
    Dim rngToSel As Range
    
    strFormula = Target.Formula
    If Left(strFormula, 6) = "=QLink" Then
        Cancel = True                 'cancel the double-click

        'Extract the reference for the range to be selected from the formula in the double-clicked cell:
        strRefToSel = Mid(strFormula, 8)
        strRefToSel = Left(strRefToSel, InStr(strRefToSel, ",") - 1)
        
        'Check the found text is a good reference:
        On Error Resume Next
        Set rngToSel = Me.Range(strRefToSel)
        On Error GoTo 0
        
        '"Jump" to the range and do other scrolling:
        If Not rngToSel Is Nothing Then
            rngToSel.Select
           'do other scrolling, etc. here
        End If
    End If
End Sub

And something like this in a cell:

=QLink(AD5:AF8,""Double click to jump"));

Related Questions

Navigate

• Browse (All)
• Browse V
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.