{"id":11827,"date":"2022-09-28T17:09:41","date_gmt":"2022-09-28T11:39:41","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-creating-macros-for-locking-range-of-cell-on-click-of-checkbox-closed\/"},"modified":"2022-09-28T17:09:41","modified_gmt":"2022-09-28T11:39:41","slug":"solved-creating-macros-for-locking-range-of-cell-on-click-of-checkbox-closed","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-creating-macros-for-locking-range-of-cell-on-click-of-checkbox-closed\/","title":{"rendered":"[Solved] creating macros for locking range of cell on click of checkbox [closed]"},"content":{"rendered":"

[ad_1]
\n<\/p>\n

\n
\n
<\/div>\n
\n
\n

Go to the worksheet of interest and:<\/p>\n

1) Click the top left to grey triangle to select all cells<\/p>\n

2) Right click Format Cells<\/p>\n

3) Ensure Locked box is ticked<\/p>\n

4) Go to Review tab and select Protect Sheet and enter your password e.g. “password”<\/p>\n

In the code associated with the check box you would have something along the lines of the following. It should give you the general elements.<\/p>\n

Private Sub CheckBox1_Click()\nDim wb as Workbook\nDim ws as Worksheet\nDim myRange as Range\nDim response As String\n\nSet wb = ThisWorkbook\nSet ws = wb.Sheets(\"mySheetName\") 'Change as appropriate\nSet myRange = ws.Range(\"A1:A2\") 'Change as appropriate\n\nIf CheckBox1.Value = True Then \n    'unlock range  \n   response = InputBox(\"Enter Password\")\n   If response <> \"password\" Then \n       Exit Sub 'Or other response such as loop to prompt again\n   Else\n       ws.Unprotect Password:=\"password\"\n       myRange.Locked = False\n   End If\nElse \n   'range should be locked\n    ws.Unprotect Password:=\"password\"\n    myRange.Locked = True\nEnd If\n\nws.Protect Password:=\"password\"\n\nEnd Sub\n<\/code><\/pre>\n
You would need to lock the VBA project so people can’t go into the VBE and retrieve the password.<\/p>\n
Some info here on locking:<\/p>\n
VBA Unlock Range<\/a><\/p>\n<\/p><\/div>\n
<\/div>\n<\/div>\n
            2<\/span> <\/p><\/div>\n<\/div>\n
[ad_2]<\/p>\n
solved creating macros for locking range of cell on click of checkbox [closed] <\/p>\n","protected":false},"excerpt":{"rendered":"
[ad_1] Go to the worksheet of interest and: 1) Click the top left to grey triangle to select all cells 2) Right click Format Cells 3) Ensure Locked box is ticked 4) Go to Review tab and select Protect Sheet and enter your password e.g. “password” In the code associated with the check box you … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[320],"tags":[400,401],"class_list":["post-11827","post","type-post","status-publish","format-standard","hentry","category-solved","tag-excel","tag-vba"],"yoast_head":"\n[Solved] creating macros for locking range of cell on click of checkbox [closed] - JassWeb<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/jassweb.com\/solved\/solved-creating-macros-for-locking-range-of-cell-on-click-of-checkbox-closed\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] creating macros for locking range of cell on click of checkbox [closed] - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] Go to the worksheet of interest and: 1) Click the top left to grey triangle to select all cells 2) Right click Format Cells 3) Ensure Locked box is ticked 4) Go to Review tab and select Protect Sheet and enter your password e.g. “password” In the code associated with the check box you ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-creating-macros-for-locking-range-of-cell-on-click-of-checkbox-closed\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-09-28T11:39:41+00:00\" \/>\n<meta name=\"author\" content=\"Kirat\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirat\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-creating-macros-for-locking-range-of-cell-on-click-of-checkbox-closed\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-creating-macros-for-locking-range-of-cell-on-click-of-checkbox-closed\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] creating macros for locking range of cell on click of checkbox [closed]\",\"datePublished\":\"2022-09-28T11:39:41+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-creating-macros-for-locking-range-of-cell-on-click-of-checkbox-closed\/\"},\"wordCount\":123,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"excel\",\"vba\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-creating-macros-for-locking-range-of-cell-on-click-of-checkbox-closed\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-creating-macros-for-locking-range-of-cell-on-click-of-checkbox-closed\/\",\"name\":\"[Solved] creating macros for locking range of cell on click of checkbox [closed] - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2022-09-28T11:39:41+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-creating-macros-for-locking-range-of-cell-on-click-of-checkbox-closed\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-creating-macros-for-locking-range-of-cell-on-click-of-checkbox-closed\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-creating-macros-for-locking-range-of-cell-on-click-of-checkbox-closed\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] creating macros for locking range of cell on click of checkbox [closed]\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/jassweb.com\/solved\/#website\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"name\":\"JassWeb\",\"description\":\"Build High-quality Websites\",\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/jassweb.com\/solved\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\",\"name\":\"Jass Web\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"contentUrl\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"width\":693,\"height\":132,\"caption\":\"Jass Web\"},\"image\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\",\"name\":\"Kirat\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1776403586\",\"contentUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1776403586\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\/\/jassweb.com\"],\"url\":\"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] creating macros for locking range of cell on click of checkbox [closed] - JassWeb","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/jassweb.com\/solved\/solved-creating-macros-for-locking-range-of-cell-on-click-of-checkbox-closed\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] creating macros for locking range of cell on click of checkbox [closed] - JassWeb","og_description":"[ad_1] Go to the worksheet of interest and: 1) Click the top left to grey triangle to select all cells 2) Right click Format Cells 3) Ensure Locked box is ticked 4) Go to Review tab and select Protect Sheet and enter your password e.g. “password” In the code associated with the check box you ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-creating-macros-for-locking-range-of-cell-on-click-of-checkbox-closed\/","og_site_name":"JassWeb","article_published_time":"2022-09-28T11:39:41+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-creating-macros-for-locking-range-of-cell-on-click-of-checkbox-closed\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-creating-macros-for-locking-range-of-cell-on-click-of-checkbox-closed\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] creating macros for locking range of cell on click of checkbox [closed]","datePublished":"2022-09-28T11:39:41+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-creating-macros-for-locking-range-of-cell-on-click-of-checkbox-closed\/"},"wordCount":123,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["excel","vba"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-creating-macros-for-locking-range-of-cell-on-click-of-checkbox-closed\/","url":"https:\/\/jassweb.com\/solved\/solved-creating-macros-for-locking-range-of-cell-on-click-of-checkbox-closed\/","name":"[Solved] creating macros for locking range of cell on click of checkbox [closed] - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-09-28T11:39:41+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-creating-macros-for-locking-range-of-cell-on-click-of-checkbox-closed\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-creating-macros-for-locking-range-of-cell-on-click-of-checkbox-closed\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-creating-macros-for-locking-range-of-cell-on-click-of-checkbox-closed\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] creating macros for locking range of cell on click of checkbox [closed]"}]},{"@type":"WebSite","@id":"https:\/\/jassweb.com\/solved\/#website","url":"https:\/\/jassweb.com\/solved\/","name":"JassWeb","description":"Build High-quality Websites","publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/jassweb.com\/solved\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/jassweb.com\/solved\/#organization","name":"Jass Web","url":"https:\/\/jassweb.com\/solved\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/","url":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","contentUrl":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","width":693,"height":132,"caption":"Jass Web"},"image":{"@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31","name":"Kirat","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1776403586","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1776403586","caption":"Kirat"},"sameAs":["http:\/\/jassweb.com"],"url":"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/"}]}},"_links":{"self":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/11827","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/comments?post=11827"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/11827\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=11827"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=11827"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=11827"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}