[marcdesmet]

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!

[savbill]

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.

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

#

[marcdesmet]

Thanks!

Marc