{"id":10712,"date":"2025-01-14T23:20:49","date_gmt":"2025-01-14T23:20:49","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2025\/01\/14\/khoa-va-mo-khoa-tat-ca-cac-sheet-cung-luc-trong-vba\/"},"modified":"2025-01-14T23:20:49","modified_gmt":"2025-01-14T23:20:49","slug":"khoa-va-mo-khoa-tat-ca-cac-sheet-cung-luc-trong-vba","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2025\/01\/14\/khoa-va-mo-khoa-tat-ca-cac-sheet-cung-luc-trong-vba\/","title":{"rendered":"Kh\u00f3a v\u00e0 m\u1edf kh\u00f3a t\u1ea5t c\u1ea3 c\u00e1c sheet c\u00f9ng l\u00fac trong VBA"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-dQf5b79p#1\">\n<p>Khi s\u1eed d\u1ee5ng\u00a0ch\u1ee9c n\u0103ng Protect Sheet trong\u00a0Excel, ch\u00fang ta ch\u1ec9 c\u00f3 th\u1ec3 kh\u00f3a t\u1eebng\u00a0trang t\u00ednh\u00a0m\u1ed9t. Nh\u01b0 v\u1eady v\u1edbi nh\u1eefng b\u1ea3ng t\u00ednh c\u00f3 nhi\u1ec1u trang t\u00ednh (workbook c\u00f3 nhi\u1ec1u sheet) th\u00ec vi\u1ec7c n\u00e0y kh\u00e1 b\u1ea5t ti\u1ec7n. \u0110\u1ec3 kh\u1eafc ph\u1ee5c v\u1ea5n \u0111\u1ec1 n\u00e0y, ch\u00fang ta c\u00f3 th\u1ec3 \u1ee9ng d\u1ee5ng VBA \u0111\u1ec3\u00a0c\u00f3 th\u1ec3 th\u1ef1c hi\u1ec7n kh\u00f3a t\u1ea5t c\u1ea3 c\u00e1c sheet c\u00f9ng l\u00fac ch\u1ec9 v\u1edbi 1 thao t\u00e1c. C\u00e1ch th\u1ef1c hi\u1ec7n nh\u01b0 sau:<\/p>\n<p><strong>B\u01b0\u1edbc 1<\/strong>: M\u1edf c\u1eeda s\u1ed5 VBA (ph\u00edm t\u1eaft Alt + F11) v\u00e0 t\u1ea1o 1 Module m\u1edbi<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-13164 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/vba-protect-multi-sheet-pic01.png\" width=\"1241\" height=\"653\" title=\"vba-protect-multi-sheet-pic01\"><\/p>\n<p><strong>B\u01b0\u1edbc 2<\/strong>: Trong Module n\u00e0y ch\u00fang ta t\u1ea1o 1 sub, l\u1ea5y t\u00ean l\u00e0 Khoa_TatCa_Sheet<\/p>\n<p>Sub Khoa_TatCa_Sheet<\/p>\n<p>\u2026<\/p>\n<p>End Sub<\/p>\n<p><strong>B\u01b0\u1edbc 3<\/strong>: Vi\u1ebft code trong sub n\u00e0y nh\u01b0 sau:<\/p>\n<div class=\"code-block code-block-2\" style=\"margin: 8px 0;clear: both\">\n<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/vba101-tu-dong-hoa-excel-voi-lap-trinh-vba-cho-nguoi-moi-bat-dau\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/vba101_92019-nho-12.gif\" title=\"vba101_92019-nho-12\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<div class=\"code-block code-block-1\" style=\"margin: 8px 0;clear: both\">\n<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/ex101-excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/ex101_92019-nho-213.gif\" title=\"ex101_92019-nho-213\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p>Vi\u1ec7c kh\u00f3a t\u1ea5t c\u1ea3 c\u00e1c sheet trong\u00a0Workbook =&gt; C\u1ea7n t\u1ea1o 1 v\u00f2ng l\u1eb7p\u00a0\u0111\u1ec3 ch\u1ea1y tr\u00ean t\u1ea5t c\u1ea3 c\u00e1c sheet trong\u00a0workbook<\/p>\n<p>C\u1ea7n c\u00f3 1 b\u1ea3ng th\u00f4ng b\u00e1o\u00a0\u0111\u1ec3 nh\u1eadp m\u1eadt kh\u1ea9u kh\u00f3a c\u00e1c sheet<\/p>\n<p>Nh\u01b0 v\u1eady ch\u00fang ta c\u1ea7n c\u00f3 2 bi\u1ebfn:<\/p>\n<ul>\n<li>Bi\u1ebfn C\u00e1c Sheet th\u00e0nh ph\u1ea7n, g\u1ecdi l\u00e0 bi\u1ebfn Ten_Sheet, bi\u1ebfn n\u00e0y\u00a0\u0111\u01b0\u1ee3c hi\u1ec3u l\u00e0 c\u00e1c Sheet trong\u00a0Excel<\/li>\n<li>Bi\u1ebfn M\u1eadt kh\u1ea9u, g\u1ecdi l\u00e0 MatKhau =&gt; D\u1ea1ng d\u1eef li\u1ec7u l\u00e0 d\u1ea1ng chu\u1ed7i k\u00fd t\u1ef1 (cho ph\u00e9p c\u1ea3 s\u1ed1 v\u00e0 ch\u1eef) =&gt; M\u1eadt kh\u1ea9u s\u1ebd\u00a0\u0111\u01b0\u1ee3c nh\u1eadp b\u1edfi ng\u01b0\u1eddi d\u00f9ng =&gt; T\u1ea1o 1 inputbox\u00a0\u0111\u1ec3 ng\u01b0\u1eddi d\u00f9ng c\u00f3 th\u1ec3 nh\u1eadp m\u1eadt kh\u1ea9u v\u00e0o\u00a0\u0111\u00e2y<\/li>\n<\/ul>\n<p>C\u00e2u l\u1ec7nh ho\u00e0n ch\u1ec9nh nh\u01b0 sau:<\/p>\n<blockquote>\n<p>Sub Khoa_TatCa_Sheet()<\/p>\n<p>Dim Ten_Sheet\u00a0as\u00a0Worksheet<\/p>\n<p>Dim MatKhauKhoa as String<\/p>\n<p>MatKhauKhoa = InputBox(\u201cNhap mat khau\u201d, vbOKCancel)<\/p>\n<p>For Each Ten_Sheet in ActiveWorkbook.Worksheets<\/p>\n<p>Ten_Sheet.Protect Password = MatKhauKhoa<\/p>\n<p>Next Ten_Sheet<\/p>\n<p>End Sub<\/p>\n<\/blockquote>\n<p>\u0110\u1ec3 m\u1edf kh\u00f3a t\u1ea5t c\u1ea3 c\u00e1c sheet c\u00f9ng l\u00fac, ch\u00fang ta l\u00e0m t\u01b0\u01a1ng t\u1ef1 nh\u01b0 v\u1eady, ch\u1ec9 thay c\u00e2u l\u1ec7nh <em>Ten_Sheet.Protect<\/em> th\u00e0nh <em>Ten_Sheet.Unprotect:<\/em><\/p>\n<blockquote>\n<p>Sub MoKhoa_TatCa_Sheet()<\/p>\n<p>Dim Ten_Sheet\u00a0as\u00a0Worksheet<\/p>\n<p>Dim MatKhauMo as String<\/p>\n<p>MatKhauMo = InputBox(\u201cNhap mat khau\u201d, vbOKCancel)<\/p>\n<p>For Each Ten_Sheet in ActiveWorkbook.Worksheets<\/p>\n<p>Ten_Sheet.Unrotect Password = MatKhauMo<\/p>\n<p>Next Ten_Sheet<\/p>\n<p>End Sub<\/p>\n<\/blockquote>\n<p>Khi ho\u00e0n th\u00e0nh 2 Sub n\u00e0y ch\u00fang ta c\u00f3 th\u1ec3 v\u1ebd ra c\u00e1c n\u00fat l\u1ec7nh (Insert&gt;Sharp) v\u00e0 g\u00e1n Macro v\u00e0o n\u00fat l\u1ec7nh\u00a0\u0111\u00f3 (Assign Macro)<\/p>\n<p><strong>Nh\u1eefng\u00a0\u0111i\u1ec3m c\u1ea7n l\u01b0u\u00a0\u00fd<\/strong>:<\/p>\n<ol>\n<li>Kh\u00f3a sheet v\u1edbi m\u1eadt kh\u1ea9u n\u00e0o th\u00ec m\u1edf kh\u00f3a v\u1edbi\u00a0\u0111\u00fang m\u1eadt kh\u1ea9u\u00a0\u0111\u00f3<\/li>\n<li>Khi kh\u00f3a sheet b\u1eb1ng c\u00e2u l\u1ec7nh VBA th\u00ec c\u00f3 th\u1ec3 b\u1ea1n s\u1ebd kh\u00f4ng t\u1ef1 m\u1edf kh\u00f3a sheet b\u1eb1ng ph\u01b0\u01a1ng ph\u00e1p th\u00f4ng th\u01b0\u1eddng\u00a0\u0111\u01b0\u1ee3c (ch\u1ecdn tab Review&gt;Unprotect sheet&gt;Nh\u1eadp m\u1eadt kh\u1ea9u\u00a0\u0111\u00e3 kh\u00f3a) =&gt; Bu\u1ed9c ph\u1ea3i k\u1ebft h\u1ee3p l\u1ec7nh M\u1edf kh\u00f3a b\u1eb1ng VBA (kh\u00f3a b\u1eb1ng VBA th\u00ec m\u1edf b\u1eb1ng VBA \u2013 l\u1ed7i n\u00e0y c\u00f3 th\u1ec3 xu\u1ea5t hi\u1ec7n\u00a0\u1edf 1 s\u1ed1 phi\u00ean b\u1ea3n Microsoft\u00a0Excel nh\u1ea5t\u00a0\u0111\u1ecbnh, n\u00ean b\u1ea1n c\u00f3 th\u1ec3 ki\u1ec3m tra l\u1ea1i xem phi\u00ean b\u1ea3n m\u00ecnh\u00a0\u0111ang d\u00f9ng c\u00f3 m\u1eafc l\u1ed7i n\u00e0y hay kh\u00f4ng)<\/li>\n<li>V\u00ec v\u1ea5n\u00a0\u0111\u1ec1 2 n\u00ean ch\u00fang ta c\u0169ng n\u00ean h\u1ea1n ch\u1ebf vi\u1ec7c thi\u1ebft l\u1eadp kh\u00f3a sheet trong VBA. Khi kh\u00f3a Sheet b\u1eb1ng VBA c\u1ea7n c\u00e2n nh\u1eafc k\u1ef9 v\u00e0 k\u1ebft h\u1ee3p thao t\u00e1c m\u1edf kh\u00f3a b\u1eb1ng c\u00e2u l\u1ec7nh VBA\u00a0\u0111\u1ec3 tr\u00e1nh xung\u00a0\u0111\u1ed9t v\u00e0 ph\u1ea3i t\u1ef1 thao t\u00e1c b\u1eb1ng tay vi\u1ec7c m\u1edf kh\u00f3a.<\/li>\n<li>C\u00f3 th\u1ec3 g\u1eafn vi\u1ec7c\u00a0\u0111\u1eb7t m\u1eadt kh\u1ea9u v\u00e0o 1 v\u1ecb tr\u00ed nh\u1ea5t\u00a0trong sheet th\u00ec ch\u00fang ta c\u00f3 th\u1ec3 qu\u1ea3n l\u00fd m\u1eadt kh\u1ea9u m\u1ed9t c\u00e1ch t\u1ed1t h\u01a1n, tr\u00e1nh b\u1ecb qu\u00ean m\u1eadt kh\u1ea9u (lu\u00f4n c\u00f3 kh\u1ea3 n\u0103ng Backup trong tr\u01b0\u1eddng h\u1ee3p x\u1ea5u: qu\u00ean m\u1eadt kh\u1ea9u, nh\u1eadp sai\u2026)<\/li>\n<\/ol>\n<p>VBA c\u00f3 r\u1ea5t nhi\u1ec1u \u1ee9ng d\u1ee5ng m\u00e0 kh\u00f4ng thao t\u00e1c \u0111\u01b0\u1ee3c tr\u00ean Excel th\u00f4ng th\u01b0\u1eddng ho\u1eb7c ph\u1ea3i m\u1ea5t r\u1ea5t nhi\u1ec1u th\u1eddi gian \u0111\u1ec3 thao t\u00e1c. Tuy nhi\u00ean, b\u1ea1n ho\u00e0n to\u00e0n c\u00f3 th\u1ec3 l\u00e0m ch\u1ee7 \u0111\u01b0\u1ee3c VBA \u0111\u1ec3 ph\u1ee5c v\u1ee5 cho c\u00f4ng vi\u1ec7c c\u1ee7a m\u00ecnh, gi\u00fap l\u00e0m t\u0103ng t\u1ed1c \u0111\u1ed9 x\u1eed l\u00fd d\u1eef li\u1ec7u, t\u1eeb \u0111\u00f3 gi\u1ea3i quy\u1ebft \u0111\u01b0\u1ee3c kh\u1ed1i l\u01b0\u1ee3ng d\u1eef li\u1ec7u l\u1edbn ch\u1ec9 trong th\u1eddi gian ng\u1eafn.<\/p>\n<p>\u0110\u1ec3 c\u00f3 th\u1ec3 h\u1ecdc VBA m\u1ed9t c\u00e1ch \u0111\u1ea7y \u0111\u1ee7, c\u00f3 h\u1ec7 th\u1ed1ng, b\u1ea1n h\u00e3y tham gia kho\u00e1 h\u1ecdc\u00a0<a href=\"http:\/\/bit.ly\/2OAUBOg\">VBA101 \u2013 VBA c\u01a1 b\u1ea3n d\u00e0nh cho ng\u01b0\u1eddi m\u1edbi b\u1eaft \u0111\u1ea7u<\/a>\u00a0c\u1ee7a h\u1ec7 th\u1ed1ng H\u1ecdc Excel Online. Hi\u1ec7n nay h\u1ec7 th\u1ed1ng \u0111ang c\u00f3 nhi\u1ec1u \u01b0u \u0111\u00e3i khi b\u1ea1n \u0111\u0103ng k\u00fd kh\u00f3a h\u1ecdc n\u00e0y. Chi ti\u1ebft xem t\u1ea1i:<\/p>\n<\/div>\n<div class=\"uk-panel uk-margin uk-width-xlarge\">\n<p>Click \u0111\u0103ng k\u00fd h\u1ecdc t\u1ea1i:<\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/khoa-hoc\" target=\"_blank\" rel=\"noopener\">T\u1ea5t c\u1ea3 kho\u00e1 h\u1ecdc\u00a0<span>\ud83d\udcd6<\/span><\/a><\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-kich-hoat-khoa-hoc-tai-hoc-excel-online.html\" target=\"_blank\" rel=\"noopener\">H\u01b0\u1edbng d\u1eabn k\u00edch ho\u1ea1t<\/a><\/p>\n<p>HKD H\u1ecdc Excel Online<br \/>S\u1ed1 \u0110K: 17A80048102<\/p>\n<\/div>\n<div class=\"uk-panel uk-text-small uk-margin uk-margin-remove-bottom uk-text-center\">\n<p>\u00a9  document.currentScript.insertAdjacentHTML(&#8216;afterend&#8217;, &#8221; + new Intl.DateTimeFormat(document.documentElement.lang, {year: &#8216;numeric&#8217;}).format() + &#8221;);  H\u1ecdc Excel Online. All rights reserved.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/vba101-tu-dong-hoa-excel-voi-lap-trinh-vba-cho-nguoi-moi-bat-dau\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/vba101_92019-nho-12.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":10713,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-10712","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-co-ban"],"_links":{"self":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/10712","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/comments?post=10712"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/10712\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/10713"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=10712"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=10712"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=10712"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}