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

完成目錄工作表

鎖定目錄避免誤觸編輯

關閉警告

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

額外功能

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