{"id":4589,"date":"2016-12-30T09:15:33","date_gmt":"2016-12-30T09:15:33","guid":{"rendered":"http:\/\/www.isunshare.com\/blog\/?p=4589"},"modified":"2023-04-13T08:30:02","modified_gmt":"2023-04-13T08:30:02","slug":"prevent-excel-sheet-from-deletion","status":"publish","type":"post","link":"https:\/\/www.isunshare.com\/blog\/prevent-excel-sheet-from-deletion\/","title":{"rendered":"2 Options to Prevent Excel Sheets from Deletion"},"content":{"rendered":"<p>Is it possible to <strong>protect all worksheets or a particular sheet from deletion<\/strong>? If it is, how can you do to restrict any users from deleting excel sheets? Here 2 options in this passage are available for you if you need, which will <strong>disable delete sheet option<\/strong> in Excel.<\/p>\n<ul>\n<li><a href=\"#option1\">Option 1: Protect Workbook structure with password<\/a><\/li>\n<li><a href=\"#option2\">Option 2: Disable &#8220;Delete&#8221; command on sheet tab with VBA<\/a><\/li>\n<\/ul>\n<h2 id=\"option1\">Option 1: Prevent Excel Sheets Deletion by Workbook Structure Protection<\/h2>\n<p>As is known, once workbook structure is protected with password, unless you could get the right password or remove password to <a href=\"https:\/\/www.isunshare.com\/excel-password\/protect-and-unprotect-workbook-structure-and-windows.html\" target=\"_blank\" rel=\"noopener noreferrer\">unprotect excel workbook structure<\/a>, otherwise, you will not insert, delete, rename, remove, copy, hide or unhide worksheets in this Excel workbook.<\/p>\n<p>So why not take this Excel feature to <strong>protect Excel sheets from being deleted<\/strong>?<\/p>\n<p><strong>1.<\/strong> <a href=\"https:\/\/www.isunshare.com\/excel-password\/top-three-ways-to-open-password-protected-excel-worksheet.html\" target=\"_blank\" rel=\"noopener noreferrer\">Open your Excel workbook<\/a> that includes worksheets you need to protect.<\/p>\n<p><strong>2.<\/strong> Tap on <strong>Review<\/strong>\u00a0menu and click <strong>Protect Workbook<\/strong> in <strong>Changes<\/strong> group.<\/p>\n<p><a href=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/choose-to-protect-workbook-structure-in-excel.png\" rel=\"attachment wp-att-4594\"><img loading=\"lazy\" decoding=\"async\" width=\"650\" height=\"236\" class=\"alignnone wp-image-4594 size-full\" src=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/choose-to-protect-workbook-structure-in-excel.png\" alt=\"choose to protect workbook structure with password\" srcset=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/choose-to-protect-workbook-structure-in-excel.png 650w, https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/choose-to-protect-workbook-structure-in-excel-300x109.png 300w\" sizes=\"auto, (max-width: 650px) 100vw, 650px\" \/><\/a><\/p>\n<p><strong>3.<\/strong> Confirm <strong>Structure<\/strong> is checked and enter a password in following dialog <strong>Protect Structure and Windows<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/enter-password-to-protect-workbook-structure.png\" rel=\"attachment wp-att-4595\"><img loading=\"lazy\" decoding=\"async\" width=\"264\" height=\"187\" class=\"alignnone wp-image-4595 size-full\" src=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/enter-password-to-protect-workbook-structure.png\" alt=\"enter password to protect workbook structure\" \/><\/a><\/p>\n<p><strong>4.<\/strong> Click <strong>OK<\/strong> and reenter the same password.<\/p>\n<p><a href=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/reenter-password-in-new-dialog.png\" rel=\"attachment wp-att-4596\"><img loading=\"lazy\" decoding=\"async\" width=\"305\" height=\"185\" class=\"alignnone wp-image-4596 size-full\" src=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/reenter-password-in-new-dialog.png\" alt=\"reeneter password to protect workbook structure\" srcset=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/reenter-password-in-new-dialog.png 305w, https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/reenter-password-in-new-dialog-300x182.png 300w\" sizes=\"auto, (max-width: 305px) 100vw, 305px\" \/><\/a><\/p>\n<p><strong>5.<\/strong> Click <strong>OK<\/strong> then you can see all the worksheets become undeleted.<\/p>\n<p><a href=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/cannot-delete-excel-sheet-without-password.png\" rel=\"attachment wp-att-4597\"><img loading=\"lazy\" decoding=\"async\" width=\"650\" height=\"251\" class=\"alignnone wp-image-4597 size-full\" src=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/cannot-delete-excel-sheet-without-password.png\" alt=\"cannot delete excel sheet without password\" srcset=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/cannot-delete-excel-sheet-without-password.png 650w, https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/cannot-delete-excel-sheet-without-password-300x116.png 300w\" sizes=\"auto, (max-width: 650px) 100vw, 650px\" \/><\/a><\/p>\n<p><strong>Tips:<\/strong> Only after unprotecting excel workbook, can you do anything again on this workbook including excel sheet deletion. If forgot password to unprotect excel workbook structure, what you need is only one click on powerful software <a href=\"https:\/\/www.isunshare.com\/workbook-unprotect-genius.html\" target=\"_blank\" rel=\"noopener noreferrer\"><strong>Workbook Unprotect Genius<\/strong><\/a> to remove forgotten password.<\/p>\n<p><a href=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/unprotect-workbook-structure.png\" rel=\"attachment wp-att-4598\"><img loading=\"lazy\" decoding=\"async\" width=\"279\" height=\"106\" class=\"alignnone wp-image-4598 size-full\" src=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/unprotect-workbook-structure.png\" alt=\"unprotect workbook to unprotect sheet from deletion\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<h2 id=\"option2\">Option 2: Prevent A Particular Sheet from Deletion with VBA<\/h2>\n<p>If you just want to prevent users from deleting one particular sheet, try this option, because <a href=\"#option1\">option 1<\/a> will not be available. But if you would like to protect all sheets from deletion, this method also works.<\/p>\n<p><strong>1.<\/strong> Open Excel workbook, and right click on any <strong>sheet tab<\/strong>. Then select <strong>View Code<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/right-click-sheet-to-open-vba-editor.png\" rel=\"attachment wp-att-4600\"><img loading=\"lazy\" decoding=\"async\" width=\"650\" height=\"251\" class=\"alignnone wp-image-4600 size-full\" src=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/right-click-sheet-to-open-vba-editor.png\" alt=\"right click sheet to open vba editor\" srcset=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/right-click-sheet-to-open-vba-editor.png 650w, https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/right-click-sheet-to-open-vba-editor-300x116.png 300w\" sizes=\"auto, (max-width: 650px) 100vw, 650px\" \/><\/a><\/p>\n<p><strong>2.<\/strong> Visual Basic Editor is opened. (Also you can press <strong>Alt + F11<\/strong> to directly open it.)<\/p>\n<p><a href=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/successfully-open-vba.png\" rel=\"attachment wp-att-4601\"><img loading=\"lazy\" decoding=\"async\" width=\"650\" height=\"332\" class=\"alignnone wp-image-4601 size-full\" src=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/successfully-open-vba.png\" alt=\"successfully open VBA editor with shortcut keyboard\" srcset=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/successfully-open-vba.png 650w, https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/successfully-open-vba-300x153.png 300w\" sizes=\"auto, (max-width: 650px) 100vw, 650px\" \/><\/a><\/p>\n<p><strong>3.<\/strong> Double-click the sheet on the left pane you want to protect, and paste following codes in the right pane.<\/p>\n<p><span style=\"color: #808080;\">Private Sub Worksheet_Deactivate()<\/span><br \/>\n<span style=\"color: #808080;\"> ThisWorkbook.Protect , True<\/span><br \/>\n<span style=\"color: #808080;\"> Application.OnTime Now, &#8220;UnprotectBook&#8221;<\/span><br \/>\n<span style=\"color: #808080;\"> End Sub<\/span><\/p>\n<p><a href=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/enter-code-to-private-excel-worksheet.png\" rel=\"attachment wp-att-4602\"><img loading=\"lazy\" decoding=\"async\" width=\"650\" height=\"332\" class=\"alignnone wp-image-4602 size-full\" src=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/enter-code-to-private-excel-worksheet.png\" alt=\"enter VBA code to protect excel sheet\" srcset=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/enter-code-to-private-excel-worksheet.png 650w, https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/enter-code-to-private-excel-worksheet-300x153.png 300w\" sizes=\"auto, (max-width: 650px) 100vw, 650px\" \/><\/a><\/p>\n<p><strong>4.<\/strong> Right-click the <strong>VBA Objects<\/strong>\u00a0then on the left and select <strong>Insert &gt; Module <\/strong>to create a module.<\/p>\n<p><a href=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/add-a-module-for-excel-vba-projects.png\" rel=\"attachment wp-att-4603\"><img loading=\"lazy\" decoding=\"async\" width=\"650\" height=\"333\" class=\"alignnone wp-image-4603 size-full\" src=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/add-a-module-for-excel-vba-projects.png\" alt=\"add a module for excel VBA project\" srcset=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/add-a-module-for-excel-vba-projects.png 650w, https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/add-a-module-for-excel-vba-projects-300x154.png 300w\" sizes=\"auto, (max-width: 650px) 100vw, 650px\" \/><\/a><\/p>\n<p><strong>5.<\/strong> Enter following codes in the right white area of Module.<\/p>\n<p><span style=\"color: #808080;\">Sub UnprotectBook()<\/span><br \/>\n<span style=\"color: #808080;\">ThisWorkbook.Unprotect<\/span><br \/>\n<span style=\"color: #808080;\">End Sub<\/span><\/p>\n<p><a href=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/add-codes-to-module-to-deactivate-workbook-or-sheet.png\" rel=\"attachment wp-att-4604\"><img loading=\"lazy\" decoding=\"async\" width=\"650\" height=\"332\" class=\"alignnone wp-image-4604 size-full\" src=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/add-codes-to-module-to-deactivate-workbook-or-sheet.png\" alt=\"add codes to module to deactivate excel workbook\" srcset=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/add-codes-to-module-to-deactivate-workbook-or-sheet.png 650w, https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/add-codes-to-module-to-deactivate-workbook-or-sheet-300x153.png 300w\" sizes=\"auto, (max-width: 650px) 100vw, 650px\" \/><\/a><\/p>\n<p><strong>6.<\/strong>\u00a0Then click <strong>Save<\/strong>\u00a0button in VBA editor and click <strong>No<\/strong> when you get a pop-up warning message &#8220;<em><span style=\"color: #808080;\">The <\/span><\/em><em><span style=\"color: #808080;\">following features cannot be saved in macro-free workbook<\/span>&#8220;.<\/em><\/p>\n<p><a href=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/confirm-to-save-macro-to-excel-workbook.png\" rel=\"attachment wp-att-4605\"><img loading=\"lazy\" decoding=\"async\" width=\"606\" height=\"179\" class=\"alignnone wp-image-4605 size-full\" src=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/confirm-to-save-macro-to-excel-workbook.png\" alt=\"confirm to save macro to excel workbook\" srcset=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/confirm-to-save-macro-to-excel-workbook.png 606w, https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/confirm-to-save-macro-to-excel-workbook-300x89.png 300w\" sizes=\"auto, (max-width: 606px) 100vw, 606px\" \/><\/a><\/p>\n<p>And in following\u00a0<strong>Save As<\/strong> dialog box, choose to save it as <strong>Excel-Macro Enabled Workbook (*.xlsm)<\/strong>\u00a0and click <strong>Save <\/strong>button.<\/p>\n<p><a href=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/choose-to-save-as-excel-macro-enabled-workbook.png\" rel=\"attachment wp-att-4606\"><img loading=\"lazy\" decoding=\"async\" width=\"625\" height=\"243\" class=\"alignnone wp-image-4606 size-full\" src=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/choose-to-save-as-excel-macro-enabled-workbook.png\" alt=\"save as excel macro-enabled workbook\" srcset=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/choose-to-save-as-excel-macro-enabled-workbook.png 625w, https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/choose-to-save-as-excel-macro-enabled-workbook-300x117.png 300w\" sizes=\"auto, (max-width: 625px) 100vw, 625px\" \/><\/a><\/p>\n<p><strong>7.<\/strong> Now open the .xlsm file in Excel and try to delete sheet in\u00a0the macro-enabled workbook.<\/p>\n<p><a href=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/try-to-delete-excel-sheet-in-macro-enabled-workbook.png\" rel=\"attachment wp-att-4607\"><img loading=\"lazy\" decoding=\"async\" width=\"650\" height=\"251\" class=\"alignnone wp-image-4607 size-full\" src=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/try-to-delete-excel-sheet-in-macro-enabled-workbook.png\" alt=\"try to delete excel sheet in macro-enabled workbook\" srcset=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/try-to-delete-excel-sheet-in-macro-enabled-workbook.png 650w, https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/try-to-delete-excel-sheet-in-macro-enabled-workbook-300x116.png 300w\" sizes=\"auto, (max-width: 650px) 100vw, 650px\" \/><\/a><\/p>\n<p>When you see a message, click &#8220;<strong>Delete<\/strong>&#8221; and you would find it is prevented from being deleted, because workbook has been\u00a0protected and cannot be changed anymore.<\/p>\n<p><a href=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/confirm-to-delete-excel-sheet.png\" rel=\"attachment wp-att-4608\"><img loading=\"lazy\" decoding=\"async\" width=\"570\" height=\"121\" class=\"alignnone wp-image-4608 size-full\" src=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/confirm-to-delete-excel-sheet.png\" alt=\"confirm to delete excel sheet\" srcset=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/confirm-to-delete-excel-sheet.png 570w, https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/confirm-to-delete-excel-sheet-300x64.png 300w\" sizes=\"auto, (max-width: 570px) 100vw, 570px\" \/><\/a><\/p>\n<p><a href=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/fail-to-delete-excel-sheet.png\" rel=\"attachment wp-att-4609\"><img loading=\"lazy\" decoding=\"async\" width=\"322\" height=\"147\" class=\"alignnone wp-image-4609 size-full\" src=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/fail-to-delete-excel-sheet.png\" alt=\"prevent deleting sheet in xlsm workbook\" srcset=\"https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/fail-to-delete-excel-sheet.png 322w, https:\/\/www.isunshare.com\/blog\/wp-content\/uploads\/2016\/12\/fail-to-delete-excel-sheet-300x137.png 300w\" sizes=\"auto, (max-width: 322px) 100vw, 322px\" \/><\/a><\/p>\n<p><strong>Related Articles:<\/strong><\/p>\n<ul>\n<li><a href=\"https:\/\/www.isunshare.com\/blog\/hide-or-unhide-sheets-in-excel-2016\/\" target=\"_blank\" rel=\"noopener noreferrer\">3 Methods to Hide or Unhide One or Multiple Sheets in Excel 2016-2007<\/a><\/li>\n<li><a href=\"https:\/\/www.isunshare.com\/excel-password\/unprotect-xls-xlsx-worksheet-without-password.html\" target=\"_blank\" rel=\"noopener noreferrer\">2 Ways to Unprotect Worksheet without Password<\/a><\/li>\n<li><a href=\"https:\/\/www.isunshare.com\/excel-password\/protect-and-unprotect-workbook-structure-and-windows.html\" target=\"_blank\" rel=\"noopener noreferrer\">How to Protect\/Unprotect Workbook Structure and Windows in Excel<\/a><\/li>\n<li><a href=\"https:\/\/www.isunshare.com\/blog\/set-excel-workbook-read-only-to-restrict-changes\/\" target=\"_blank\" rel=\"noopener noreferrer\">How to Set Excel Workbook Read Only to Restrict Changes<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Is it possible to protect all worksheets or a particular sheet from deletion? If it is, how can&#8230;<\/p>\n","protected":false},"author":1,"featured_media":126192,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[19],"tags":[230],"class_list":["post-4589","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-office-documents","tag-excel"],"_links":{"self":[{"href":"https:\/\/www.isunshare.com\/blog\/wp-json\/wp\/v2\/posts\/4589","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.isunshare.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.isunshare.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.isunshare.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.isunshare.com\/blog\/wp-json\/wp\/v2\/comments?post=4589"}],"version-history":[{"count":5,"href":"https:\/\/www.isunshare.com\/blog\/wp-json\/wp\/v2\/posts\/4589\/revisions"}],"predecessor-version":[{"id":6494,"href":"https:\/\/www.isunshare.com\/blog\/wp-json\/wp\/v2\/posts\/4589\/revisions\/6494"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.isunshare.com\/blog\/wp-json\/wp\/v2\/media\/126192"}],"wp:attachment":[{"href":"https:\/\/www.isunshare.com\/blog\/wp-json\/wp\/v2\/media?parent=4589"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.isunshare.com\/blog\/wp-json\/wp\/v2\/categories?post=4589"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.isunshare.com\/blog\/wp-json\/wp\/v2\/tags?post=4589"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}