Unanswered: Follow hyperlink a make targetrow the toprow
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).
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.
' 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"
' 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
' Select row 1 on the sheet
' 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
On Error GoTo isRange_Err
Set x = Range(strRange)
isRange = True
isRange = False