跳到主要內容

如何設置密碼以保護Excel中的隱藏工作表?

如果您的工作簿包含一些重要的隱藏工作表,並且不允許其他人取消隱藏它們。 現在,您需要設置一個密碼來完全保護隱藏的工作表,其他用戶取消隱藏它們時,他們必須輸入密碼。 您是否有任何方法可以在Excel中快速輕鬆地處理此任務?

設置密碼以使用VeryHidden功能保護隱藏的工作表

設置密碼以使用VBA代碼保護隱藏的工作表

使用Kutools for Excel一次保護多個工作表


設置密碼以使用VeryHidden功能保護隱藏的工作表

通常,您可以使用VeryHidden函數先隱藏工作表,然後為它們設置密碼,請執行以下步驟:

1。 打開工作簿,然後按住 Alt + F11鍵鍵打開 Microsoft Visual Basic for Applications窗口.

2。 在 Microsoft Visual Basic for Applications窗口點擊此處成為Trail Hunter 瀏覽 > 項目瀏覽器 屬性窗口o顯示其窗格。

文檔保護隱藏表 01

3. 然後在 項目-VBA項目 窗格中,選擇要隱藏的工作表,然後在 氟化鈉性能 窗格中,單擊 可見 選擇部分 xlSheet非常隱藏 選項,請參見屏幕截圖:

文檔保護隱藏表 02

4。 使工作表非常隱藏之後,您可以設置密碼來保護它。 在裡面 Microsoft Visual Basic for Applications窗口點擊此處成為Trail Hunter 插入 > 模塊 打開一個空的模塊,然後單擊 工具 > VBAProject屬性,請參見屏幕截圖:

文檔保護隱藏表 03

5。 然後在彈出 VBAProject-項目屬性 對話框,單擊 保護的故事 選項卡,然後檢查 鎖定項目以供查看 框,最後,輸入並確認密碼 查看項目屬性的密碼 部分,請參見屏幕截圖:

文檔保護隱藏表 04

6。 然後點擊 OK 按鈕退出此對話框,然後關閉 Microsoft Visual Basic for Applications窗口。

7。 將工作簿另存為 Excel啟用宏的工作簿 格式化並關閉它以使密碼保護生效。

文檔保護隱藏表 05

8。 下次,當您打開此工作簿並希望使隱藏的工作表可見時,要求您輸入密碼。 看截圖:

文檔保護隱藏表 06


演示:設置密碼以保護隱藏的工作表


設置密碼以使用VBA代碼保護隱藏的工作表

要設置密碼來保護隱藏的工作表,我還可以為您介紹一個VBA代碼。

1。 隱藏一個您要保護的工作表。

2。 按住 ALT + F11 鍵打開 Microsoft Visual Basic for Applications窗口.

3。 然後選擇 的ThisWorkbook 從左邊 項目瀏覽器,雙擊以打開 模塊,然後將以下VBA代碼複製並粘貼到空白模塊中:

VBA代碼:設置密碼以保護隱藏的工作表

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim xSheetName As String
xSheetName = "Sheet1"
If Application.ActiveSheet.Name = xSheetName Then
    Application.EnableEvents = False
    Application.ActiveSheet.Visible = False
    xTitleId = "KutoolsforExcel"
    response = Application.InputBox("Password", xTitleId, "", Type:=2)
    If response = "123456" Then
        Application.Sheets(xSheetName).Visible = True
        Application.Sheets(xSheetName).Select
    End If
End If
Application.Sheets(xSheetName).Visible = True
Application.EnableEvents = True
End Sub

文檔保護隱藏表 07

備註:在上面的代碼中, Sheet1 xSheetName =“ Sheet1” 腳本是您要保護的隱藏工作表名稱,並且 123456 ,在 如果響應=“ 123456”,則 腳本是您為隱藏工作表設置的密碼。 您可以根據需要更改它們。

4。 現在,當您要顯示隱藏的工作表時,將彈出一個提示框,讓您輸入密碼。 每次單擊以顯示隱藏的工作表時,都會出現此提示框。

文檔保護隱藏表 08


使用Kutools for Excel一次保護多個工作表

通常,如果要一次保護一個工作簿的多個選定的工作表或所有工作表,通常需要在Excel中逐個手動保護。 但是,如果你有 Excel的Kutools,其 保護工作表 實用程序,您可以一鍵保護它們。

Excel的Kutools : 帶有300多個便捷的Excel加載項,可以在30天內免費試用

安裝後 Excel的Kutools,請執行以下操作:

1。 點擊 Kutools 加 > 保護工作表,請參見屏幕截圖:

2。 在 保護工作表 對話框中,工作簿中的所有工作表都列在列錶框中,請選擇要保護的工作表。 看截圖:

文檔保護隱藏表 010

3。 然後點擊 OK,請在以下對話框中輸入您的密碼,然後再次輸入該密碼,然後單擊 OK,將彈出另一個提示框,提醒您已保護了多少個工作表。

文檔保護隱藏表 011

4。 然後點擊 OK 關閉對話框,並且工作簿中的所有工作表均已使用相同的密碼保護。

注意: 如果要一次取消保護所有工作表,只需單擊 Kutools 加 > 取消保護工作表,然後輸入密碼以取消保護。

 立即下載和免費試用Excel的Kutools!


相關文章:

如何在Excel中一次保護多個工作表?

如何設置密碼來保護工作簿?

如何在Excel中保護/鎖定VBA代碼?

最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

kte選項卡201905


Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
Comments (10)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
jak ktoś ukrył arkusz jak w opisanym pierwszym sposobie, a nie znamy hasła to jak odkryć arkusz skoro hasło do visual basic jest w ukrytym arkuszu?
This comment was minimized by the moderator on the site
Hello, ciekawa

Sorry, at present, there is no good way for canceling the protect of the hidden sheet if you forget the password.

But, you can apply the Uhide all hidden sheets feature of Kutools for Excel to unhide all the hidden sheets without any password.
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-unhide-sheets.png
You can download Kutools for Excel and use it freely for 30 days.
Please have a try. Thank you!
This comment was minimized by the moderator on the site
This is elegant. IF the person opening the spreadsheet does not enable macros (i.e. VBA) what happens? Is the hidden sheet a sitting duck? Or is it quite impossible to find?
This comment was minimized by the moderator on the site
Hi, David,
If you open the workbook without enabling the macro, the hidden sheet is displayed as normal. In this case, I recommend you to apply the first method for solving this job.
Thank you!
This comment was minimized by the moderator on the site
Your code allows users to view the sheet as long as they hold the left mouse button while the mouse pointer is on the tab. Try this to keep sheet hidden until correct password is entered: If response = "123456" Then Application.Sheets(xSheetName).Visible = True Application.Sheets(xSheetName).Select Else Application.Sheets(xSheetName).Visible = False[/b][/b] End If End If Application.EnableEvents = True End Sub
This comment was minimized by the moderator on the site
I have a question about your password restricted worksheet code. You posted the following code which works....what I am looking for is code that will do this with multiple worksheets and multiple passwords within the same workbook. Is this possible? Private Sub Workbook_SheetActivate(ByVal Sh As Object) 'Update 20140925 Dim xSheetName As String xSheetName = "sheet1" If Application.ActiveSheet.Name = xSheetName Then Application.EnableEvents = False Application.ActiveSheet.Visible = False xTitleId = "KutoolsforExcel" response = Application.InputBox("Password", xTitleId, "", Type:=2) If response = "123456" Then Application.Sheets(xSheetName).Visible = True Application.Sheets(xSheetName).Select End If End If Application.Sheets(xSheetName).Visible = True Application.EnableEvents = True End Sub
This comment was minimized by the moderator on the site
Found a solution yet?

I am having the same problem
This comment was minimized by the moderator on the site
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim aSheetName As String
Dim bSheetName As String
Dim cSheetName As String
aSheetName = "sheet1"
bSheetName = "sheet2"
cSheetName = "sheet3"

If Application.ActiveSheet.Name = aSheetName Then
Application.EnableEvents = False
Application.ActiveSheet.Visible = False
xTitleId = "KutoolsforExcel"
response = Application.InputBox("Password", xTitleId, "", Type:=2)
If response = "123" Then
Application.Sheets(aSheetName).Visible = True
Application.Sheets(aSheetName).Select
Else
Application.Sheets(aSheetName).Visible = False
End If
End If
Application.EnableEvents = True

If Application.ActiveSheet.Name = bSheetName Then
Application.EnableEvents = False
Application.ActiveSheet.Visible = False
xTitleId = "KutoolsforExcel"
response = Application.InputBox("Password", xTitleId, "", Type:=2)
If response = "456" Then
Application.Sheets(bSheetName).Visible = True
Application.Sheets(bSheetName).Select
Else
Application.Sheets(bSheetName).Visible = False
End If
End If
Application.EnableEvents = True

If Application.ActiveSheet.Name = cSheetName Then
Application.EnableEvents = False
Application.ActiveSheet.Visible = False
xTitleId = "KutoolsforExcel"
response = Application.InputBox("Password", xTitleId, "", Type:=2)
If response = "789" Then
Application.Sheets(cSheetName).Visible = True
Application.Sheets(cSheetName).Select
Else
Application.Sheets(cSheetName).Visible = False
End If
End If
Application.EnableEvents = True

End Sub
This comment was minimized by the moderator on the site
Thanks for the code, just have one question. When you open the sheet you are asked for question, then you enter it and the sheet is visible, but when you jump to next sheet and try to re-open previous sheet, you are asked for the same password again everytime. My question is can you bypass that and make excel ask for password once?
This comment was minimized by the moderator on the site
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim xSheetName As String
aSheetName = "sheet1"
bSheetName = "sheet2"
cSheetName = "sheet3"

If Application.ActiveSheet.Name = aSheetName Then
Application.EnableEvents = False
Application.ActiveSheet.Visible = False
xTitleId = "KutoolsforExcel"
response = Application.InputBox("Password", xTitleId, "", Type:=2)
If response = "1234" Then
Application.Sheets(aSheetName).Visible = True
Application.Sheets(aSheetName).Select
Else
Application.Sheets(aSheetName).Visible = False
End If
End If
Application.EnableEvents = True

If Application.ActiveSheet.Name = bSheetName Then
Application.EnableEvents = False
Application.ActiveSheet.Visible = False
xTitleId = "KutoolsforExcel"
response = Application.InputBox("Password", xTitleId, "", Type:=2)
If response = "2345" Then
Application.Sheets(bSheetName).Visible = True
Application.Sheets(bSheetName).Select
Else
Application.Sheets(bSheetName).Visible = False
End If
End If
Application.EnableEvents = True

If Application.ActiveSheet.Name = cSheetName Then
Application.EnableEvents = False
Application.ActiveSheet.Visible = False
xTitleId = "KutoolsforExcel"
response = Application.InputBox("Password", xTitleId, "", Type:=2)
If response = "3456" Then
Application.Sheets(cSheetName).Visible = True
Application.Sheets(cSheetName).Select
Else
Application.Sheets(cSheetName).Visible = False
End If
End If
Application.EnableEvents = True

End Sub
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations