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

or

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

假設您有一個包含多個工作表的工作簿,現在您需要保護所有工作表或某些特定的工作表(通常在Excel中),您只能使用保護工作表功能逐個保護工作表,但此方法非常繁瑣,如果需要保護大量紙張,耗時。 如何快速輕鬆地在Excel中一次保護多張表格?

用VBA代碼一次性保護所有表格

用Kutools for Excel一次保護多張紙

使用kutools for Excel一次解除多個工作表的保護


一次保護使用相同密碼的多個工作表:

Kutools for Excel保護工作表 - 取消保護工作表,您可以同時使用相同的密碼保護或取消保護工作簿中的所有工作表。

文檔保護,多重片狀物,9

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


箭頭藍色右泡 用VBA代碼一次性保護所有表格


使用以下VBA代碼,您可以使用相同的密碼一次性保護活動工作簿的所有工作表,請執行以下操作:

1。 按住 ALT + F11 鍵,然後打開 Microsoft Visual Basic for Applications 窗口。

2。 點擊 插入 > 模塊,並將以下代碼粘貼到 模塊窗口.

VBA代碼:一次保護工作簿中的所有工作表:

Sub protect_all_sheets()
top: 
pass = InputBox("password?") 
repass = InputBox("Verify Password") 
If Not (pass = repass) Then 
MsgBox "you made a boo boo" 
Goto top 
End If 
For i = 1 To Worksheets.Count 
If Worksheets(i).ProtectContents = True Then Goto oops 
Next 
For Each s In ActiveWorkbook.Worksheets 
s.Protect Password:=pass 
Next 
Exit Sub 
oops: MsgBox "I think you have some sheets that are already protected. Please unprotect all sheets then running this Macro." 
End Sub

3。 然後按 F5 鍵運行代碼,然後在提示框中輸入受保護工作表的密碼,請參閱屏幕截圖:

文檔保護,多sheets1-1

4。 點擊 OK,然後再次輸入密碼以確認密碼。

文檔保護,多sheets2-2

5。 然後點擊 OK,並且所有工作表都使用相同的密碼進行了保護。


箭頭藍色右泡 用Kutools for Excel一次保護多張紙

有時,您不想保護工作簿中的所有工作表,只想保護一些特定工作表,在這種情況下,上述代碼將無法工作。 但是與 Kutools for Excel保護工作表 實用程序,您可以快速輕鬆地保護所有紙張和特定紙張。

Kutools for Excel : 與超過300方便的Excel加載項,在60天免費試用沒有限制.

安裝Kutools for Excel後,請按以下步驟操作:

1。 點擊 企業 > 保護工作表,看截圖:

文檔保護,多sheets3-3

2。 在 保護工作表 對話框中,選擇要保護的圖紙。 (默認情況下,將檢查當前工作簿中的所有工作表。)請參見截圖:

文檔保護,多sheets4-4

3。 並點擊 OK,然後在“保護工作表”對話框中鍵入並確認密碼。 看截圖:

文檔保護,多sheets5-5

4。 然後點擊 OK,選定的工作表已被保護。

單擊保護工作表以了解有關此功能的更多信息。


箭頭藍色右泡 使用kutools for Excel一次解除多個工作表的保護

你怎麼能同時在工作簿中保護多個受保護的工作表? 當然, Kutools for Excel 還提供了 取消保護工作表 實用程序讓您立即取消它們的保護。

Kutools for Excel : 與超過300方便的Excel加載項,在60天免費試用沒有限制.

請遵循以下步驟:

1。 打開包含受保護工作表的工作簿。

2。 然後點擊 企業 > 取消保護工作表,看截圖:

文檔保護,多sheets6-6

3。 在 取消保護工作表 對話框中,受保護的工作表被列入列錶框中,然後單擊 Ok 按鈕,看截圖:

文檔保護,多sheets7-7

4。 然後a 取消保護工作表 彈出對話框提醒您輸入您為保護工作表而創建的密碼,請參閱截圖:

文檔保護,多sheets8-8

5。 輸入密碼後,單擊 Ok,列錶框中的所有選中的工作表都不受保護。

請注意: 工作表必須具有相同的密碼。

了解更多關於此解除保護工作表功能。

立即下載並免費試用Kutools for Excel!


箭頭藍色右泡 演示:使用Kutools for Excel一次保護或取消保護所有工作表

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


相關文章:

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


Kutools for Excel解決了您的大多數問題,並使您的生產率提高了80%

  • 重用: 快速插入 複雜的公式,圖表 以及你以前用過的任何東西; 加密單元格 密碼; 創建郵件列表 並發送電子郵件...
  • 超級方程式酒吧 (輕鬆編輯多行文字和公式); 閱讀佈局 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 不丟失數據; 分裂細胞含量; 組合重複的行/列...防止重複的細胞; 比較範圍...
  • 選擇複製或唯一 行; 選擇空行 (所有細胞都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇......
  • 精確複製 多個單元格而不更改公式參考; 自動創建參考 多張表; 插入項目符號,複選框等等......
  • 提取文本,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級過濾器 (將過濾方案保存並應用到其他工作表); 高級排序 按月/週/日,頻率等; 特殊過濾器 用粗體,斜體......
  • 結合工作簿和工作表; 根據鍵列合併表; 將數據拆分為多個表格; 批量轉換xls,xlsx和PDF...
  • 超過300強大的功能。 支持Office / Excel 2007-2019和365。 支持所有語言。 在您的企業或組織中輕鬆部署。 全功能30天免費試用。
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.
    George · 3 years ago
    I am trying to use this in a shared workbook but I get a Microsoft Visual Basic error "Run-time error '1004": Application-defined or object-defined error" I am just looking for suggestions on how to get around this. Any help would be appreciated
  • To post as a guest, your comment is unpublished.
    Geetha · 3 years ago
    Thanks for the wonder full tool to protect & unprotect all the sheets at a time.
    Please guide me I am able to unprotect all sheets in my work book at a time but when I run protect all sheets VBA Code then only first 4 sheets are protected & rest or left unprotected. Please help me to sort out the issue.
  • To post as a guest, your comment is unpublished.
    Mayhem · 4 years ago
    How do I mask the password using the VBA method?

    I can not find this information via google or F1 help files.

    Thanks in advance!
  • To post as a guest, your comment is unpublished.
    James MacPherson · 4 years ago
    Hi I used this code now I it wont accept my password to get back into my spreadsheet !!
  • To post as a guest, your comment is unpublished.
    S. Brown · 4 years ago
    I have chart tabs and worksheets in my Excel 2013 workbook. The VBA coding above works for the protecting the worksheets but not the separate chart tabs. How do i password protect the multiple chart tabs and multiple worksheets in my workbook? Thanks!
  • To post as a guest, your comment is unpublished.
    Zuber · 4 years ago
    Hey,

    I need some help, the above code was very helpful, but it locks the sort (auto filter as well) can you please provide code to solve that problem and also where it would be inserted.

    Thank you in advance.
  • To post as a guest, your comment is unpublished.
    N1ckole · 5 years ago
    Thank you! This is awesome, I was even able to modify the VBA code so that I could unprotect all sheets at once! :)

    Sub unprotect_all_sheets()
    top:
    pass = InputBox("password?")
    repass = InputBox("Verify Password")
    If Not (pass = repass) Then
    MsgBox "you made a boo boo"
    GoTo top
    End If
    For i = 1 To Worksheets.Count
    Next
    For Each s In ActiveWorkbook.Worksheets
    s.Unprotect Password:=pass
    Next
    Exit Sub
    oops: MsgBox "I think you have some sheets that are already protected. Please unprotect all sheets B4 running this Macro."
    End Sub
  • To post as a guest, your comment is unpublished.
    etan · 5 years ago
    when i activated this VBA, [b]hyperlink [/b]doesn't work. how can i make them work? tnx :)
  • To post as a guest, your comment is unpublished.
    Lightdemon · 5 years ago
    I am suddenly having the 1004 error as well. Anyone find a solution?
  • To post as a guest, your comment is unpublished.
    Lightdemon · 5 years ago
    I'm suddenly having the error 1004 issue. Have been using the macro for months now it randomly fails.
  • To post as a guest, your comment is unpublished.
    Scruffly Ghengis · 5 years ago
    when I imput the code it comes up with "run time error 1004?? highlighting the following in yellow...

    s.Protect Password:=pass
    • To post as a guest, your comment is unpublished.
      Lightdemon · 5 years ago
      I'm suddenly having the 1004 error as well. I have been using the macro for months but now it won't run.
      • To post as a guest, your comment is unpublished.
        Michelle McGregor · 3 years ago
        Hi,

        I was having this issue too. Turns out it can't run if you have all sheets selected in the workbook.

        Try selecting just one sheet and then running.
  • To post as a guest, your comment is unpublished.
    MrB · 5 years ago
    As Kristin said, how do you unprotect them all at once?
    • To post as a guest, your comment is unpublished.
      cz · 5 years ago
      So, how do I unprotect without kutools?
      • To post as a guest, your comment is unpublished.
        Admin-chivo · 5 years ago
        [quote name="cz"]So, how do I unprotect without kutools?[/quote]

        You can unprotect the worksheet without Kutools for Excel. You just need to right click on the sheet tab which you want to unprotect and click Unprotect Sheet command, and then enter your password. :-)
  • To post as a guest, your comment is unpublished.
    shweta · 6 years ago
    Thank you so much :-)
  • To post as a guest, your comment is unpublished.
    Kristin Dzugan · 6 years ago
    Great--so how to UNprotect them all at once? I'm an amateur, so I need more info.
    • To post as a guest, your comment is unpublished.
      N1ckol3 · 5 years ago
      Sub unprotect_all_sheets()
      top:
      pass = InputBox("password?")
      repass = InputBox("Verify Password")
      If Not (pass = repass) Then
      MsgBox "you made a boo boo"
      GoTo top
      End If
      For i = 1 To Worksheets.Count
      Next
      For Each s In ActiveWorkbook.Worksheets
      s.Unprotect Password:=pass
      Next
      Exit Sub
      oops: MsgBox "I think you have some sheets that are already protected. Please unprotect all sheets B4 running this Macro."
      End Sub
    • To post as a guest, your comment is unpublished.
      Admin_jay · 5 years ago
      [quote name="Kristin Dzugan"]Great--so how to UNprotect them all at once? I'm an amateur, so I need more info.[/quote]

      :-) You can easily unprotect them as http://www.extendoffice.com/product/kutools-for-excel/excel-unprotect-multiple-sheets.html