{"id":11279,"date":"2019-10-25T13:01:08","date_gmt":"2019-10-25T11:01:08","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=11279"},"modified":"2024-02-06T16:16:26","modified_gmt":"2024-02-06T15:16:26","slug":"protect-all-worksheets-using-macros-in-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/protect-all-worksheets-using-macros-in-excel\/","title":{"rendered":"Protect All Worksheets Using Macros In Excel"},"content":{"rendered":"<p>You can <strong>protect all worksheets<\/strong> using <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/101-excel-macros-examples\/\" target=\"_blank\" rel=\"noopener\" title=\"Macros\" data-wpil-keyword-link=\"linked\">Macros<\/a> in Excel! You will ask the user for the password then use it to protect all of the worksheets in the file.<\/p>\n<p>Make sure your Excel has the Developer Tab enabled following\u00a0<a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/how-to-create-your-first-macro-button-in-excel-beginners-tutorial\/\" target=\"_blank\" rel=\"noopener noreferrer\">this tutorial<\/a>.<\/p>\n<p>I explain how you can do this below step by step!<\/p>\n<p><em><strong>What does it do?<\/strong><\/em><\/p>\n<p>Protects all worksheets with the user-given password<\/p>\n<p><em><strong>Copy Source Code:<\/strong><\/em><\/p>\n<pre class=\"brush: vb; title: ; notranslate\" title=\"\">\r\n\r\nSub ProtectAllWorksheets()\r\n\r\nDim worksheet As Worksheet\r\nDim pword As String\r\n\r\n'Get the password from the user\r\npword = InputBox(&amp;amp;quot;Enter a Password to Protect All Worksheets&amp;amp;quot;, &amp;amp;quot;Password&amp;amp;quot;)\r\n\r\n'Loop through the worksheets and protect all of them\r\nFor Each worksheet In ActiveWorkbook.Worksheets\r\nworksheet.Protect Password:=pword \r\nNext worksheet\r\n\r\nEnd Sub\r\n\r\n<\/pre>\n<p><em><strong>Final Result:\u00a0<\/strong><\/em><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Protect All Worksheets Using Macros In Excel\"  class=\"aligncenter size-full wp-image-11806\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/03\/Protect-All-Worksheets-05.jpg\" alt=\"Protect All Worksheets Using Macros In Excel\" width=\"1583\" height=\"723\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/03\/Protect-All-Worksheets-05.jpg 1583w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/03\/Protect-All-Worksheets-05-300x137.jpg 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/03\/Protect-All-Worksheets-05-1024x468.jpg 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/03\/Protect-All-Worksheets-05-768x351.jpg 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/03\/Protect-All-Worksheets-05-1536x702.jpg 1536w\" sizes=\"(max-width: 1583px) 100vw, 1583px\" \/><\/p>\n<p><em><strong>Exercise Workbook:<\/strong><\/em><\/p>\n<div class=\"after-post-box\">\n<p><iframe src=\"https:\/\/www.youtube.com\/embed\/x8P34it47Po?rel=0 \" width=\"800\" height=\"400\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\"><span data-mce-type=\"bookmark\" style=\"display: inline-block; width: 0px; overflow: hidden; line-height: 0;\" class=\"mce_SELRES_start\">\ufeff<\/span><\/iframe><a href=\"https:\/\/www.youtube.com\/c\/MyExcelOnlinecomtutorials?sub_confirmation=1\" target=\"_blank\" rel=\"noopener noreferrer nofollow\"><img loading=\"lazy\" decoding=\"async\" class=\"alignmiddle wp-image-111468 size-full\" style=\"vertical-align: middle;\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/04\/1.yt_.png\" alt=\"\" title=\"\"><\/a><\/p>\n<p class=\"line-two\"><a class=\"IRPP_minimalist icon-download button small\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/08\/Protect-All-Worksheets.xlsm\" target=\"_blank\" rel=\"noopener noreferrer\" data-leadbox=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/03\/Protect-All-Worksheets.xlsm\" data-leadbox-id=\"147284833f72a2:12f08263ab46dc\"><span class=\"ctaText px-3 text-capitalize\">Download excel workbook<i class=\"fa fa-download\"><\/i><\/span><span class=\"postTitle px-3\">Protect-All-Worksheets.xlsm<\/span><\/a><\/p>\n<p class=\"line-two\">\n<\/div>\n<hr \/>\n<p>This is our list of worksheets:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Protect All Worksheets Using Macros In Excel\"  class=\"aligncenter size-full wp-image-11802\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/03\/Protect-All-Worksheets-01.jpg\" alt=\"Protect All Worksheets Using Macros In Excel\" width=\"1124\" height=\"670\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/03\/Protect-All-Worksheets-01.jpg 1124w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/03\/Protect-All-Worksheets-01-300x179.jpg 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/03\/Protect-All-Worksheets-01-1024x610.jpg 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/03\/Protect-All-Worksheets-01-768x458.jpg 768w\" sizes=\"(max-width: 1124px) 100vw, 1124px\" \/><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 1:<\/strong><\/span>\u00a0Go to\u00a0<em><strong>Developer &gt; Code &gt; Visual Basic<\/strong><\/em><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-10906 aligncenter\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/01\/How-to-Insert-Multiple-Columns-01.jpg\" alt=\"\" width=\"816\" height=\"139\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/01\/How-to-Insert-Multiple-Columns-01.jpg 816w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/01\/How-to-Insert-Multiple-Columns-01-300x51.jpg 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/01\/How-to-Insert-Multiple-Columns-01-768x131.jpg 768w\" sizes=\"(max-width: 816px) 100vw, 816px\" title=\"\"><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 2:<\/strong><\/span>\u00a0Paste in your code and\u00a0<strong>Select Save<\/strong>. Close the window afterwards.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Protect All Worksheets Using Macros In Excel\"  class=\"aligncenter size-full wp-image-12861\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/08\/Protect-All-Worksheets-02.jpg\" alt=\"Protect All Worksheets Using Macros In Excel\" width=\"1431\" height=\"513\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/08\/Protect-All-Worksheets-02.jpg 1431w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/08\/Protect-All-Worksheets-02-300x108.jpg 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/08\/Protect-All-Worksheets-02-1024x367.jpg 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/08\/Protect-All-Worksheets-02-768x275.jpg 768w\" sizes=\"(max-width: 1431px) 100vw, 1431px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 3:\u00a0<\/strong><\/span>Let us test it out!<\/p>\n<p>Go to\u00a0<em><strong>Developer &gt; Code &gt; Macros<\/strong><\/em><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-11245\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/03\/Unmerge-Cells-02.jpg\" alt=\"\" width=\"854\" height=\"142\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/03\/Unmerge-Cells-02.jpg 854w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/03\/Unmerge-Cells-02-300x50.jpg 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/03\/Unmerge-Cells-02-768x128.jpg 768w\" sizes=\"(max-width: 854px) 100vw, 854px\" title=\"\"><\/p>\n<p>&nbsp;<\/p>\n<p>Make sure your <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/101-excel-macros-examples\/\" target=\"_blank\" rel=\"noopener\" title=\"macro\" data-wpil-keyword-link=\"linked\">macro<\/a> is selected. Click <strong>Run<\/strong>.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Protect All Worksheets Using Macros In Excel\"  class=\"aligncenter size-full wp-image-11810\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Protect-All-Worksheets-03.jpg\" alt=\"Protect All Worksheets Using Macros In Excel\" width=\"613\" height=\"576\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Protect-All-Worksheets-03.jpg 613w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Protect-All-Worksheets-03-300x282.jpg 300w\" sizes=\"(max-width: 613px) 100vw, 613px\" \/><\/p>\n<p>Type in a password and <strong>click OK<\/strong>.<\/p>\n<p><span style=\"color: #0000ff;\"><strong><img loading=\"lazy\" decoding=\"async\" title=\"Protect All Worksheets Using Macros In Excel\"  class=\"aligncenter size-full wp-image-12862\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/08\/Protect-All-Worksheets-04.jpg\" alt=\"Protect All Worksheets Using Macros In Excel\" width=\"597\" height=\"250\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/08\/Protect-All-Worksheets-04.jpg 597w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/08\/Protect-All-Worksheets-04-300x126.jpg 300w\" sizes=\"(max-width: 597px) 100vw, 597px\" \/><\/strong><\/span><\/p>\n<p>With just one click, <strong>all of your worksheets are now protected<\/strong>!<\/p>\n<p><span style=\"color: #0000ff;\"><strong>\u00a0<img loading=\"lazy\" decoding=\"async\" title=\"Protect All Worksheets Using Macros In Excel\"  class=\"aligncenter size-full wp-image-11806\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/03\/Protect-All-Worksheets-05.jpg\" alt=\"Protect All Worksheets Using Macros In Excel\" width=\"1583\" height=\"723\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/03\/Protect-All-Worksheets-05.jpg 1583w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/03\/Protect-All-Worksheets-05-300x137.jpg 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/03\/Protect-All-Worksheets-05-1024x468.jpg 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/03\/Protect-All-Worksheets-05-768x351.jpg 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/03\/Protect-All-Worksheets-05-1536x702.jpg 1536w\" sizes=\"(max-width: 1583px) 100vw, 1583px\" \/><\/strong><\/span><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #0000ff;\"><strong>How to Protect All Worksheets Using Macros In Excel<\/strong><\/span><\/p>\n<p>&nbsp;<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/100-66.html\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-13266\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/10\/101-Macros-Book.png\" alt=\"101 Macros Book\" width=\"1200\" height=\"628\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/10\/101-Macros-Book.png 1200w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/10\/101-Macros-Book-300x157.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/10\/101-Macros-Book-1024x536.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/10\/101-Macros-Book-768x402.png 768w\" sizes=\"(max-width: 1200px) 100vw, 1200px\" title=\"\"><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>You can protect all worksheets using Macros in Excel! You will ask the user for the password then use it to protect all of the worksheets in the file. Make sure your Excel has the Developer Tab enabled following\u00a0this tutorial. I explain how you can do this below step by step! What does it do? [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":18067,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[56,646],"tags":[106],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/11279"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=11279"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/11279\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/18067"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=11279"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=11279"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=11279"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}