Results 1 to 3 of 3
  1. #1
    Join Date
    May 2002
    Posts
    3

    Unanswered: Follow hyperlink a make targetrow the toprow

    Hi,

    I have a hyperlink in a sheet, attached to shape in the same sheet.
    Now when that link is clicked, I want the cell to which the hyperlink is directed, to be in the first visible row in the window (usually it will be in the last visible row).

    Anyone a solution?

    Thanx!

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by marcdesmet
    I want the cell to which the hyperlink is directed, to be in the first visible row in the window (usually it will be in the last visible row).
    This poses a problem because a shape with the hyperlink will run the link and will not execute code. The way to do it would be to write code for the action, assign the sub process (macro) to the shape to run the action and don't even use a hyperlink method. But if you must have a hyperlink you could use a workaround to defeat the hyperlink (bookmark) action and run your own code to perform the row move. I did this by creating 2 shape objects, placing a tranparent shape with a macro assigned on top of the shape with the hyperlink. Here's a code example for checking the link and inserting the row at row 1 top of the sheet, using a tranparent shape overtop the shape with a link assinged.
    Code:
    Sub Oval_2_Click()
    ' check the name of the hyperlink bookmark range
    strLinkRange = ActiveSheet.Shapes("AutoShape 1").Hyperlink.Name
    
    ' see if range is valid
    If Not isRange(strLinkRange) Then
     MsgBox "Not a valid bookmark"
     Exit Sub
    End If
    
    ' set a variable for the bookmarked row
    intRow = Range(strLinkRange).Row
    
    ' cut and insert the row at row 1, or row 2 if sheet has header row
    Rows(intRow).Cut
    Rows(1).Insert Shift:=xlDown
    
    ' Select row 1 on the sheet
    Range("A1").Select
    
    ' Here you could update the hyperlink value to be Row 1, or 2 if header row
    ' but then the macro woul dbe obsolete for following clicks
    
    End Sub
    
    
    Function isRange(strRange)
    On Error GoTo isRange_Err
    
    Set x = Range(strRange)
    isRange = True
    isRange_Exit:
        Exit Function
    isRange_Err:
        isRange = False
    End Function

    #
    ~

    Bill

  3. #3
    Join Date
    May 2002
    Posts
    3
    Thanks!

    Marc

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •