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

園長

發佈於園長的遊戲手札

2022/04/25閱讀時間約 3 分鐘

因為我有一個有超過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，執行「取消隱藏所有工作表」即可

為什麼會看到廣告

園長

你好，我是園長，我喜歡玩遊戲、聊遊戲、也在做遊戲，偶爾也會做點別的事

留言0

查看全部

發表第一個留言支持創作者！