{"id":146,"date":"2012-11-27T15:39:25","date_gmt":"2012-11-27T15:39:25","guid":{"rendered":"http:\/\/192.168.50.22\/index.php\/2012\/11\/27\/unlocklock-all-sheets-excel\/"},"modified":"2026-03-15T22:00:46","modified_gmt":"2026-03-16T02:00:46","slug":"unlocklock-all-sheets-excel","status":"publish","type":"post","link":"https:\/\/smegnl.medina.oh.us\/?p=146","title":{"rendered":"Unlock\/Lock all sheets Excel"},"content":{"rendered":"<h5>Unprotect all Worksheets<\/h5>\n<p>Open a workbook and start the Visual Basic Editor (Alt + F11). Right Click in the project explorer window and select Insert &gt; Module. Copy the following code into the new Module then select Run and all worksheets will be Unprotected in that Workbook<\/p>\n<pre style=\"margin: 0px; padding: 0px; border: 0px; outline: 0px; font-size: 12px; vertical-align: baseline; font-family: monospace; width: auto; clear: none; overflow: visible; line-height: 1.333; color: #110000; background-color: #f9f9f9;\"><span style=\"margin: 0px; padding: 0px; border: 0px; outline: 0px; font-style: inherit; vertical-align: baseline; color: #000080;\">Sub<\/span> UnlockWorksheets()\n<span style=\"margin: 0px; padding: 0px; border: 0px; outline: 0px; font-style: inherit; vertical-align: baseline; color: #008000;\">'This routine will go through and Unprotect all the worksheets for the workbook\n<\/span><span style=\"margin: 0px; padding: 0px; border: 0px; outline: 0px; font-style: inherit; vertical-align: baseline; color: #008000;\">'Change the Password to any required value or leave blank\n<\/span>\n<span style=\"margin: 0px; padding: 0px; border: 0px; outline: 0px; font-style: inherit; vertical-align: baseline; color: #000080;\">Dim<\/span> wsWorksheet                 <span style=\"margin: 0px; padding: 0px; border: 0px; outline: 0px; font-style: inherit; vertical-align: baseline; color: #000080;\">As<\/span> Worksheet\n&nbsp;\n    <span style=\"margin: 0px; padding: 0px; border: 0px; outline: 0px; font-style: inherit; vertical-align: baseline; color: #000080;\">For<\/span> <span style=\"margin: 0px; padding: 0px; border: 0px; outline: 0px; font-style: inherit; vertical-align: baseline; color: #000080;\">Each<\/span> wsWorksheet <span style=\"margin: 0px; padding: 0px; border: 0px; outline: 0px; font-style: inherit; vertical-align: baseline; color: #000080;\">In<\/span> ActiveWorkbook.Worksheets\n        wsWorksheet.Unprotect Password:=<span style=\"margin: 0px; padding: 0px; border: 0px; outline: 0px; font-style: inherit; vertical-align: baseline; color: #800000;\">\"password\"<\/span>\n    <span style=\"margin: 0px; padding: 0px; border: 0px; outline: 0px; font-style: inherit; vertical-align: baseline; color: #000080;\">Next<\/span>\n&nbsp;\n<span style=\"margin: 0px; padding: 0px; border: 0px; outline: 0px; font-style: inherit; vertical-align: baseline; color: #000080;\">End<\/span> <span style=\"margin: 0px; padding: 0px; border: 0px; outline: 0px; font-style: inherit; vertical-align: baseline; color: #000080;\">Sub<\/span><\/pre>\n<h5>Protect all Worksheets<\/h5>\n<p>Open a workbook and start the Visual Basic Editor (Alt + F11). Right Click in the project explorer window and select Insert &gt; Module. Copy the following code into the new Module then select Run and Worksheet Protection will be enabled for all worksheets in that Workbook<\/p>\n<pre style=\"margin: 0px; padding: 0px; border: 0px; outline: 0px; font-size: 12px; vertical-align: baseline; font-family: monospace; width: auto; clear: none; overflow: visible; line-height: 1.333; color: #110000; background-color: #f9f9f9;\"><span style=\"margin: 0px; padding: 0px; border: 0px; outline: 0px; font-style: inherit; vertical-align: baseline; color: #000080;\">Sub<\/span> LockWorksheets()\n<span style=\"margin: 0px; padding: 0px; border: 0px; outline: 0px; font-style: inherit; vertical-align: baseline; color: #008000;\">'This routine will go through and Protect all the worksheets for the workbook\n<\/span><span style=\"margin: 0px; padding: 0px; border: 0px; outline: 0px; font-style: inherit; vertical-align: baseline; color: #008000;\">'Change the Password to any required value or leave blank\n<\/span>\n<span style=\"margin: 0px; padding: 0px; border: 0px; outline: 0px; font-style: inherit; vertical-align: baseline; color: #000080;\">Dim<\/span> wsWorksheet                 <span style=\"margin: 0px; padding: 0px; border: 0px; outline: 0px; font-style: inherit; vertical-align: baseline; color: #000080;\">As<\/span> Worksheet\n&nbsp;\n    <span style=\"margin: 0px; padding: 0px; border: 0px; outline: 0px; font-style: inherit; vertical-align: baseline; color: #000080;\">For<\/span> <span style=\"margin: 0px; padding: 0px; border: 0px; outline: 0px; font-style: inherit; vertical-align: baseline; color: #000080;\">Each<\/span> wsWorksheet <span style=\"margin: 0px; padding: 0px; border: 0px; outline: 0px; font-style: inherit; vertical-align: baseline; color: #000080;\">In<\/span> ActiveWorkbook.Worksheets\n        wsWorksheet.Protect Password:=<span style=\"margin: 0px; padding: 0px; border: 0px; outline: 0px; font-style: inherit; vertical-align: baseline; color: #800000;\">\"password\"<\/span>\n    <span style=\"margin: 0px; padding: 0px; border: 0px; outline: 0px; font-style: inherit; vertical-align: baseline; color: #000080;\">Next<\/span>\n&nbsp;\n<span style=\"margin: 0px; padding: 0px; border: 0px; outline: 0px; font-style: inherit; vertical-align: baseline; color: #000080;\">End<\/span> <span style=\"margin: 0px; padding: 0px; border: 0px; outline: 0px; font-style: inherit; vertical-align: baseline; color: #000080;\">Sub<\/span><\/pre>\n<p>Note: Worksheet Protection is only enforced within those cells of a Worksheet that have been Locked. In a future post we will demonstrate how Cell Locking works and how you can use it to develop robust spreadsheet solutions.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Unprotect all Worksheets Open a workbook and start the Visual Basic Editor (Alt + F11). Right Click in the project explorer window and select Insert &gt; Module. Copy the following code into the new Module then select Run and all worksheets will be Unprotected in that Workbook Sub UnlockWorksheets() &#8216;This routine will go through and [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":3014,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-146","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-figured-it-out"],"_links":{"self":[{"href":"https:\/\/smegnl.medina.oh.us\/index.php?rest_route=\/wp\/v2\/posts\/146","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/smegnl.medina.oh.us\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/smegnl.medina.oh.us\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/smegnl.medina.oh.us\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/smegnl.medina.oh.us\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=146"}],"version-history":[{"count":0,"href":"https:\/\/smegnl.medina.oh.us\/index.php?rest_route=\/wp\/v2\/posts\/146\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/smegnl.medina.oh.us\/index.php?rest_route=\/wp\/v2\/media\/3014"}],"wp:attachment":[{"href":"https:\/\/smegnl.medina.oh.us\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=146"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/smegnl.medina.oh.us\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=146"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/smegnl.medina.oh.us\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=146"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}