2022-04-25|閱讀時間 ‧ 約 4 分鐘

在Excel製作好用的「目錄」工作表

因為我有一個有超過30個工作表的Excel檔，直接在畫面下方捲動選取實在太慢了，所以就找了製作目錄的方法，只要點擊目錄的連結，就會自動取消隱藏該工作表並移動過去，回到目錄後又會自動隱藏目標工作表，用起來還不錯

步驟如下：

完成目錄工作表

選取所有工作表，並在最左方插入一個Column，在A1的儲存格中輸入「=XDF1」

選取檔案-資訊-檢查活頁簿-檢查相容性，再按下「複製到新工作表」，如此就會自動產生包含所有工作表的連結。

建立一個新的工作表，並且把連結複製過去，並且隨自己的意思進行美化。視情況看要不要關閉自動換行，這會影響是否要把整個儲存格當作連結。

然後就可以再次選取剛剛選的所有工作表，並且刪掉剛剛插入的Column，或者也可以把第一行做成回目錄的連結。

之所以要鎖定目錄，是因為不鎖定的話，按連結會變成編輯

校閱-保護工作表，確定即可

關閉警告

在目錄的工作表上按右鍵-檢視程式碼，在專案物件處選擇目錄的工作表

將這份Code貼到目錄的地方，不然會一直跳錯誤訊息

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    If Target.Cells(1, 1).Locked Then Cancel = True

End Sub

接著上面的警告插入這個Code

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

    Dim Sh As Object

    On Error Resume Next

    Set Sh = Sheets(Replace(Split(Target.SubAddress, "!")(0), "'", ""))

    If Not Sh Is Nothing Then Sh.Visible = xlSheetVisible

End Sub

然後在ThisWorkbook插入這份Code

Private Sub Workbook_Open()

End Sub

Private Sub Workbook_Activate()

　　Application.StatusBar = "隱藏"

End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

　　If Sh.CodeName <> "代號" Then Sh.Visible = xlHidden

End Sub

然後把「代號」二字換成工作表的Name，也就是在VBA下面的代號，例如我這邊叫做「工作表1」

至此目錄功能已經完成

如果之後還要增加工作表，只要直接新增，然後取消鎖定、加上連結、再重新鎖定就好，Code都不用改

有需要一次取消隱藏所有目錄表的話，可以使用這份Code

Sub 取消隱藏所有工作表()

Application.ScreenUpdating = False

Dim i As Integer

For i = 1 To Sheets.Count

    Sheets(i).Visible = True

Next i

Application.ScreenUpdating = True

End Sub

右鍵-插入-模組，然後貼進去

在需要使用的時候按Alt+F8，執行「取消隱藏所有工作表」即可

分享至

成為作者繼續創作的動力吧！