提示:其他語言是Google翻譯的。 你可以訪問 English 版本。
登入
x
or
x
x
註冊
x

or

如何在Excel中跟踪超鏈接到隱藏表格?

例如,我有一個包含多個工作表的工作簿,第一個主工作表具有超鏈接到其他工作表,現在,我隱藏除第一個表之外的所有工作表。 在這種情況下,鏈接到隱藏工作表的超鏈接不可用。 但是,你怎麼能讓這些超鏈接成功運行? 當您單擊一個超鏈接時,鏈接的隱藏表將立即打開,如下圖所示:

doc超鏈接到隱藏工作表1

按照超鏈接使用VBA代碼打開隱藏表格

按照超鏈接打開隱藏表格,然後用VBA代碼再次隱藏它


列出所有工作表名稱並鏈接到每個工作表:

Kutools for Excel's 創建圖紙名稱列表 實用程序,您可以根據需要快速列出帶有超鏈接或宏按鈕的新工作表名稱。

帶有超鏈接的工作表名稱 帶有宏按鈕的工作表名稱
文檔列表,工作表,names2  2 文檔列表,工作表,names2  2 文檔列表,工作表,names2

按照超鏈接使用VBA代碼打開隱藏表格


以下VBA代碼可能會幫助您在單擊其相關超鏈接時打開隱藏的工作表,請按照以下步驟操作:

1。 右鍵單擊要跟踪超鏈接到隱藏工作表的工作表選項卡,然後選擇 查看代碼 從上下文菜單中,彈出 Microsoft Visual Basic for Applications 窗口,請將以下代碼複製並粘貼到空白模塊中:

VBA代碼:按照超鏈接打開隱藏工作表:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Updateby Extendoffice 20161109
    Application.ScreenUpdating = False
    On Error Resume Next
    If Target.Column = 1 Then
        Sheets(Target.Value).Visible = xlSheetVisible
        Sheets(Target.Value).Select
    End If
    Application.ScreenUpdating = True
End Sub

doc超鏈接到隱藏工作表2

注意:在上面的代碼中,數字 1 在這 如果Target.Column = 1那麼 腳本指示包含超鏈接的列號,請更改您的需要。

2。 然後保存並關閉此代碼窗口,現在,當您單擊鏈接到特定隱藏工作表的超鏈接時,隱藏工作表將立即打開。


按照超鏈接打開隱藏表格,然後用VBA代碼再次隱藏它

有時,您需要按照超鏈接打開隱藏工作表,當您返回主工作表時,您希望再次隱藏打開的工作表。 下面的VBA代碼可以幫你一個忙:

1。 右鍵單擊要跟踪超鏈接到隱藏工作表的工作表選項卡,然後選擇 查看代碼 從上下文菜單中,彈出 Microsoft Visual Basic for Applications 窗口,請將以下代碼複製並粘貼到空白模塊中:

VBA代碼:按照超鏈接打開隱藏表格,當再次隱藏時:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
   'Updateby Extendoffice 20161109
    Application.ScreenUpdating = False
    Dim strLinkSheet As String
    If InStr(Target.Parent, "!") > 0 Then
        strLinkSheet = Left(Target.Parent, InStr(1, Target.Parent, "!") - 1)
    Else
        strLinkSheet = Target.Parent
    End If
    Sheets(strLinkSheet).Visible = True
    Sheets(strLinkSheet).Select
    Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_Activate()
    On Error Resume Next
    Sheets(ActiveCell.Value2).Visible = False
End Sub

doc超鏈接到隱藏工作表3

2。 然後保存廣告關閉代碼窗口,當您單擊超鏈接時,鏈接的隱藏表格將立即打開,但如果您返回包含超鏈接的主表單,打開的表單將自動再次隱藏。

注意:這些代碼僅適用於與超鏈接文本相匹配的表單名稱。


演示:按照超鏈接使用VBA代碼打開隱藏表格

Kutools for Excel:比200方便的Excel加載項,可以在60天免費試用。 下載並免費試用現在!


Kutools for Excel - 最佳辦公生產力工具提高80%的生產力

  • 重用: 快速插入 複雜的公式,圖表 以及你以前用過的任何東西; 加密單元格 密碼; 創建郵件列表 並發送電子郵件...
  • 超級方程式酒吧 (輕鬆編輯多行文字和公式); 閱讀佈局 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 不丟失數據; 分裂細胞含量; 組合重複的行/列...防止重複的細胞; 比較範圍...
  • 選擇複製或唯一 行; 選擇空行 (所有細胞都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇......
  • 精確複製 多個單元格而不更改公式參考; 自動創建參考 多張表; 插入項目符號,複選框等等......
  • 提取文本,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級過濾器 (將過濾方案保存並應用到其他工作表); 高級排序 按月/週/日,頻率等; 特殊過濾器 用粗體,斜體......
  • 結合工作簿和工作表; 根據鍵列合併表; 將數據拆分為多個表格; 批量轉換xls,xlsx和PDF...
  • 超過300強大的功能。 支持Office / Excel 2007-2019和365。 支持所有語言。 在您的企業或組織中輕鬆部署。 全功能60天免費試用。
kte tab 201905

Office選項卡為Office提供選項卡式界面,使您的工作更輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,Publisher,Access,Visio和Project。
  • 在同一窗口的新選項卡中打開並創建多個文檔,而不是在新窗口中。
  • 通過50%提高您的工作效率,每天為您減少數百次鼠標點擊!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Ken · 1 years ago
    I would like to use this code but only want it to apply to column A. I have other web address links in other cells which cause a problem when running this code.
  • To post as a guest, your comment is unpublished.
    DNKP · 2 years ago
    My hyperlink "text" is number only, and it doesn't work...
  • To post as a guest, your comment is unpublished.
    Brad Guiso · 2 years ago
    I'm using the code on two different sheets. It works for one of about 40 hyperlinks on one sheet and it opens two of about 10 hyperlinks one she second, the sheets on the second which do open do go back into hiding when I return to the sheet.

    The error I get when it will not open a sheet is Run-time error"9": script out of range and the code it points me to - Sheets(strLinkSheet).Visible = True

    Any ideas as to what change is needed make the code work on all of the sheets?

    Thank you in advance.
  • To post as a guest, your comment is unpublished.
    HelzBelz · 2 years ago
    I have used this code on two different sheets in my workbook, and in both cases it works great, except for the first hyperlink on each page. I get "Subscript out of range error" and the debugger points to the "Sheets(strLinkSheet).Visible = True" right after the "end if".

    Anyone else see this error? It is only on the first link on the page. (my hyperlinks are in column A, and start in row 2)
    • To post as a guest, your comment is unpublished.
      Bumi · 2 years ago
      [quote name="HelzBelz"]I have used this code on two different sheets in my workbook, and in both cases it works great, except for the first hyperlink on each page. I get "Subscript out of range error" and the debugger points to the "Sheets(strLinkSheet).Visible = True" right after the "end if".

      Anyone else see this error? It is only on the first link on the page. (my hyperlinks are in column A, and start in row 2)[/quote]
      Sheet names can't have space like "Sheet 1". I fixed my sheet names to "Sheet1" and the error stopped.
  • To post as a guest, your comment is unpublished.
    Regina Hardaway · 2 years ago
    I have a sheet that is hidden that contains a hyperlink. I would like to be able go to the hidden sheet and have it close when I go back to the original sheet. I used the code but it does not work.
  • To post as a guest, your comment is unpublished.
    Regina Hardaway · 2 years ago
    I am trying to hide a sheet that has a hyperlink. I would like to be able to click on the sheet go to the hyperlink sheet and then click back to the sheet and close the hidden sheet. I have tried your instructions but it does not work. Not sure what I am doing wrong.