excel - 选择超链接目标单元格的整行并向左滚动目标单元格

Question

单击超链接并到达目标单元格后，我想选择（突出显示）目标单元格的整行，如果它太靠右，则向左滚动，以便目标单元格在屏幕的中间列周围，大约 5可以看到其右侧的列。在查看了 stackoverflow 中的类似帖子后，我使用了 FollowHyperlink()。但是，代码没有错误但没有做任何事情。我该如何重写代码来解决这个问题？

Sub CreateHyperlink()
...
If i > 1 Then
    .Hyperlinks.Add ...
End If
End Sub    

Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

ActiveWindow.ScrollColumn = Target.Column
Target.EntireRow.Select

End Sub

标签： excelvba

Answer 1

I think this is what you want?

Option Explicit

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim ws As Worksheet
    Dim rng As Range

    '~~> Get the name of the worksheet
    Set ws = ThisWorkbook.Sheets(Split(Target.SubAddress, "!")(0))

    With ws
        '~~> Get the range
        Set rng = .Range(Split(Target.SubAddress, "!")(1))

        '~~> Unhide it, in case it is hidden
        .Visible = xlSheetVisible

        '~~> Go To that cell
        Application.Goto rng, Scroll:=True

        '~~> Select the row and activate it so that it comes in the center
        rng.EntireRow.Select
        rng.Activate
    End With
End Sub

My assumptions

The target range is a single cell in the same workbook. If you still want to scroll 5 columns more then you can use ActiveWindow.SmallScroll ToRight:=-5. Amend it as per your requirement.

Note: This code Worksheet_FollowHyperlink goes in the sheet code area.