时间戳

首页 > 解决方案 > 如何超链接到同一张工作表中的多个隐藏选项卡？

excel - 如何超链接到同一张工作表中的多个隐藏选项卡？

问题描述

我有一个超链接列表，指向工作簿中的多个不同隐藏工作表，每个使用以下内容：

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Application.ScreenUpdating = False
    Worksheets("LL - JLL").Visible = xlSheetVisible
    Sheets("LL - JLL").Visible = True
    Sheets("LL - JLL").Select
    Application.ScreenUpdating = True
End Sub

据我所知，这现在适用于工作表上的每个超链接。现在，每个超链接都指向同一张纸，LL - JLL而我需要每个超链接指向不同的纸。例如，

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Application.ScreenUpdating = False
    Worksheets("LL - JLL").Visible = xlSheetVisible
    Sheets("LL - JLL").Visible = True
    Sheets("LL - JLL").Select
    Application.ScreenUpdating = True

    Application.ScreenUpdating = False
    Worksheets("LL - EMS").Visible = xlSheetVisible
    Sheets("LL - EMS").Visible = True
    Sheets("LL - EMS").Select
    Application.ScreenUpdating = True

    Application.ScreenUpdating = False
    Worksheets("LL- CCURE").Visible = xlSheetVisible
    Sheets("LL- CCURE").Visible = True
    Sheets("LL- CCURE").Select
    Application.ScreenUpdating = True
End Sub

以下代码使工作表上的所有超链接都指向工作LL-CURE表，而不是它们对应的工作表。

为不同的超链接创建一个新的 Sub 会导致

Compile error:
Ambiguous name detected: Worksheet_FolowHyperlink

任何指导将不胜感激:)

标签： excelvba

解决方案

逻辑：

  1. 找到超链接指向的范围
  2. 查找上述范围所指的工作表的名称
  3. 将名称传递给公共子以取消隐藏并激活工作表

代码：

这是你正在尝试的吗？

Option Explicit

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim rng As Range
    
    '~~> Get the range the hyperlink is referrig to
    Set rng = Application.Evaluate(Target.SubAddress)
    
    '~~> Unhide and activate the sheet
    UnHideAndActivate rng.Parent.Name
End Sub

Private Sub UnHideAndActivate(shName As String)
    Dim scrnUpdating As Boolean
    Dim dsplyAlerts As Boolean
    
    On Error GoTo Whoa
    
    With Application
        '~~> Get user's current setting
        scrnUpdating = .ScreenUpdating
        dsplyAlerts = .DisplayAlerts
        
        '~~> Set it to necessary setting
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
    
    '~~> Unhide and activate the sheet
    Worksheets(shName).Visible = xlSheetVisible
    Worksheets(shName).Activate
LetsContinue:
    With Application
        '~~> Reset original settings
        .ScreenUpdating = scrnUpdating
        .DisplayAlerts = dsplyAlerts
    End With
    
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

推荐阅读