قفل کردن اکسل | به کمک کدهای VBA هر چیزی را قفل کنید
سلام به یکی دیگر از مطالب مربوط به آموزش برنامه نویسی VBA در اکسل خوش آمدید . موضوع آموزش امروزمان در زمینه قفل کردن اجزای اکسل به کمک کدهای VBA است.
اما همین ابتدای کار بیایید دو تا چیز را مشخص کنیم .
اول اینکه چرا باید اجزای اکسل را قفل کنیم و اصلا چه نیازی به این کار داریم ؟
دوم اینکه وقتی که صحبت از اجزای اکسل می کنیم منظورمان دقیقا چه چیزی می تواند باشد .
پس بدون هیچ مقدمه دیگری به سراغ اصل آموزش خودمان برویم .
چه نیازی به قفل کردن اجزای اکسل داریم ؟
خوب دلایل متعددی برای این کار وجود دارد . فرض کنید که شما یک سری از اطلاعات را در داخل یک شیت اکسل قرار داده اید که هر گونه تغییری در ساختار این اطلاعات می تواند مشکلات زیادی را برای شما ایجاد کند .
اطلاعاتی مثل میزان حقوق افراد ، مشخصات مربوط به کد ملی ، شماره تماس یا هر جور اطلاعات دیگری از این دست نباید قایل تغییر باشند .
یا اینکه در داخل یک یا چند از شیت های اکسل کلی تابع و فرمول استفاده کرده اید که محاسبات مختلفی برای شما انجام می دهند .
پس اینجا خیلی مهم است که به نحوی از این فرمولها محافظت کنید تا کسی آنها را تغییر ندهد .
یک ایده مناسب این است که همه سلول های حاوی فرمول را قفل کنید
پس به عنوان یک ایده و راهکار اجرایی می توانید به سراغ این گزینه بروید که بعد از وارد کردن و تکمیل این اطلاعات به نحوی آنها را قفل گذاری کنید تا از بابت تغییرات ناخواسته خیالتان راحت شود .
چه اجزایی از اکسل را می شود قفل گذاری کرد ؟
اکسل این امکان را به من و شما می دهد که هر بخشی را که مورد نظرمان است را قفل کنیم .
بعضی وقتها لازم است یک سلول را قفل کنیم . بعضی وقتها لازم است یک محدوده از سلول ها را قفل کنیم یا کل یک شیت را قفل کنیم و یا حتی کل یک فایل اکسل را قفل کنیم .
خبر خوب این است که اکسل این امکان را برای ما فراهم کرده است که هر کدام از این اجزا را بطور جداگانه قفل کنیم .
چه روش هایی برای قفل کردن اجزای اکسل وجود دارد ؟
بطور کلی دو تا روش برای این کار وجود دارد .
روش اول استفاده از امکاناتی است که خود اکسل در اختیار ما قرار می دهد .
روش دوم هم استفاده از برنامه نویسی و کدهای VBA برای قفل گذاری اجزای اکسل است .
در این آموزش ما بطور مشخص بر روی روش دوم تمرکز می کنیم .
می خواهیم با هم کدهایی را بنویسیم که به کمک آنها هر بخش از اکسل را که مد نظرمان است را قفل کنیم .
پس بیایید کارمان را شروع کنیم .
چطور با کدهای VBA اجزای اکسل را قفل کنیم ؟
زبان برنامه نویسی VBA برای قفل گذاری اجزای مختلف اکسل از یک دستور ساده استفاده می کند .
اول بیایید با این دستور آشنا شویم تا بعد برویم به سراغ استفاده از آن .
به ساختار زیر نگاه کنید .
expression.Protect (Password, Structure, Windows)
اول بیایید آرگومانهای این تابع را بررسی کنیم .
password: آرگومان اول پسوردی است که برای آن بخش مورد نظر که می خواهیم قفل گذاری کنیم انتخاب می کنیم . البته انتخاب یک پسورد می تواند حالت اختیاری داشته باشد .
structure: این آرگومان دو تا حالت دارد یا true است یا false .
windows: این آرگومان هم دو تا حالت دارد یا true است یا false .
اما منظور از این دو تا آرگومان آخری چیست و چه کاری قرار است انجام بدهند ؟
پاسخ این است که وقتی که ما از طریق دستی یک بخش از اکسل را قفل گذاری می کنیم اکسل یک پنجره ای مثل پنجره زیر را به ما نمایش می دهد .
به دو تا چک باکس در قسمت پایین این کادر نگاه کنید . اینجا دو تا گزینه داریم که دقیقا هم نام با آرگومانهایی هستند که در بالا به آنها اشاره کردم . پس کار این آرگومانها در برنامه نویسی دقیقا شبیه سازی کاری است که در این کادر انجام می دهیم .
اما شاید بپرسید این دو تا آرگومان چه کاری می کنند ؟
بهتر است عجله نکنید در ادامه همین آموزش و در داخل یک ویدئو به کار این دو تا آرگومان اشاره خواهم کرد .
حالا بهتر است شروع به قفل کردن اجزای اکسل کنیم .
چطور بخش های مختلف اکسل را قفل گذاری کنیم ؟
گفتم که ما به کمک برنامه نویسی VBA در اکسل می توانیم قسمت های مختلف اکسل را قفل کنیم . بیایید این کار را از ساده ترین اجزا شروع کنیم .
1- قفل گذاری یک سلول خاص در اکسل
فرض کنید که می خواهیم سلول A1 که در داخل sheet1 قرار دارد را قفل کنیم .
برای این کار از کد ساده زیر استفاده می کنیم .
Range("a1").Activate
ActiveCell.Locked = True
این کدها را در داخل یک روال وارد کرده و اجرا کنید . فکر می کنید چه اتفاقی می افتد ؟ من به شما می گویم قرار نیست با این دو تا خط کد اتفاقی رخ بدهد .
اما چرا ؟
دلیلش این است که اکسل به حالت پیشفرض تمامی شیتهای کار خود را به حالت محافظت شده قرار داده است .
برای اینکه بتوانیم یک سلول را قفل کنیم اول باید کل شیت را از حالت محافظت شده خارج کنیم .
حالا کدهای زیر را امتحان کنید .
Sub lock_cells()
ActiveSheet.Protect Contents:=False
Range("a1").Activate
ActiveCell.Locked = True
ActiveSheet.Protect Contents:=True
End Sub
حالا به سراغ سلول A1 رفته و سعی کنید در داخل آن چیزی را درج کنید . این بار چه اتفاقی می افتد ؟
بله این بار به محض زدن دکمه اینتر اکسل کادر پیغام زیر را به شما نمایش می دهد .
این یعنی اینکه سلول مورد نظر شما در حال حاضر قفل شده است .
2- قفل گذاری یک محدوده خاص در اکسل
قفل کردن یک محدوده دقیقا مثل این است که یک سلول را می خواهید قفل کنید . فقط به جای اسم یک سلول خاص این بار باید یک محدوده را به اکسل معرفی کنید . مثل کدهای زیر .
Sub lock_cells()
ActiveSheet.Protect Contents:=False
Range("a1:d10").Activate
ActiveCell.Locked = True
ActiveSheet.Protect Contents:=True
End Sub
در کدهای بالا ما از اکسل می خواهیم که محدوده سلول های a1 تا d10 را برای ما قفل کند .
قفل کردن سلول هایی که حاوی فرمول هستند .
در این بخش می خواهیم کار قفل کردن سلول های موجود در یک شیت را هدفمند تر کنیم . به چه صورت ؟
می خواهیم کاری کنیم که اکسل هر سلولی را که در داخل یک شیت کاری دارای فرمول است را برای ما قفل گذاری کند .
اینطوری خیالمان راحت می شود که کسی نمی تواند در محاسبات ما دستکاری کند . اما چطور این کار را انجام می دهیم ؟
برای این کار می توانیم از کدهایی مثل کدهای زیر استفاده کنیم .
dim myrange as Range
dim mycell as Range
set myrange=range("A1:d10")
For Each mycell In myrange
If mycell.HasFormula Then
mycell.Locked = True
Else
mycell.Locked = False
End If
Next mycell
اینجا از اکسل می خواهیم که سلول های موجود در محدوده A1 تا d10 را بررسی کرده و هر سلولی که حاوی فرمول بود را برای ما قفل گذاری کند .
قفل کردن یک شیت کاری در اکسل
قبل از این ما یک سلول ، یک محدوده از سلولها و سلول هایی را که حاوی فرمول بودند را با استفاده از برنامه نویسی وی بی ای قفل گذاری کردیم .
حالا برویم به سراغ اینکه چطور می شود یک شیت کاری را در اکسل قفل گذاری کنیم . برای این کار از کدهای زیر استفاده می کنیم .
Sheets("Sheet1").protect
با این خط کد ساده ما از اکسل می خواهیم که از مجموعه شیتهای موجود شیتی با عنوان sheet 1 را برای ما به حالت محافظت شده دربیاورد .
این ساده ترین حالتی است که با آن یک ورک شیت را قفل می کنیم . اما بعضی از مواقع هم هست که می خواهیم در برای محافظت بیشتر از فایل اکسل مان برای آن یک پسورد یا گذر واژه هم در نظر بگیریم .
برای این کار می توانیم از کدهایی مثل کد زیر استفاده کنیم .
Sheets("Sheet1").protect password:="softpluse"
قفل کردن یک ورک بوک در اکسل
در این قسمت می خواهیم به سراغ ورک بوک ها برویم و ببینیم چطور می شود یک ورک بوک را قفل گذاری کرد . برای قفل کردن یک ورک بوک می توانیم از کدهایی مثل کد زیر استفاده کنیم .
Workbooks("mysoftpluse").Protect
اینجا من یک فایل اکسل دارم به نام mysoftpluse و با استفاده از این کد می خواهم که این فایل محافظت شود .
آموزش امروز مان هم در همین جا به اتمام می رسد . در این آموزش با هم روش های مختلفی را یاد گرفتیم که به کمک آنها می شود هر بخشی از اکسل را که مد نظرمان باشد را قفل گذاری کنیم .
البته من از این روش بصورت کاملا کاربردی و در عمل در دوره تعیین سطح دسترسی برای کاربران در اکسل استفاده کرده ام .
اگر علاقمند هستید توصیه می کنم که حتما این دوره را تهیه و از آن استفاده کنید .
در ضمن توی پیج اینستاگرام هم کلی مطالب کاربردی در زمینه اکسل و برنامه نویسی VBA در اکسل وجود دارد که می توانید از آنها هم استفاده کنید .
خوشحال می شوم نظر شما را در مورد این آموزش در انتهای همین مطلب داشته باشم .
موفق باشید .
مطالب زیر را حتما مطالعه کنید
چطور زبان ورود اطلاعات در اکسس را کنترل کنیم ؟
با رعایت این نکات تبدیل به یک برنامه نویس حرفه ای در اکسل شوید .
۷ نکته مهم در برنامه نویسی VBA که باید آنها را بدانید
ماکرو نویسی در اکسل |ببینید که ماکروها چقدر کاربردی اند.
زبان وی بی ای مرده است
۱۰ کاری که بدون برنامه نویسی VBA قادر به انجامش نبودید .
توی این آموزش کارهایی رو بررسی می کنیم که فقط با استفاده از کدهای وی بی ای می شود آنها را در آفیس انجام داد .
12 دیدگاه
به گفتگوی ما بپیوندید و دیدگاه خود را با ما در میان بگذارید.
من میخوام چند تا سلول هربه رنگ خاصی هستد رو قفل کنم چکار باید بکنم
بزودی بهش خواهیم پرداخت
درود – فرمول را مینویسم اما ارور میده
Sub loching()
With ActiveSheet
.Unprotect
.Cells.lock = False
.Cells.SpecialCells(xlCellTypeFormulas).Locked = True
.Protect AllowDeletingRows:=True
End With
End Sub
خطای شما مربوط به این خط از کدها است
.Cells.lock = False
چون شی cells اصلا همچین متدی ندارد
اگر می خواهید سلول های حاوی فرمول را قفل کنید از کدهای مربوط به “قفل کردن سلول های حاوی فرمول” در همین مطلب استفاده کنید .
سلام. من یک فایل اکسل دارم که شامل ۴ شیت هست. با استفاده از فرم لاگین ۴ کاربر مختلف میتوانند لاگین کنند.
من خوام هر کاربری که لاگین کرد فقط شیت مخصوص به خودش قابل ویرایش باشه و مابقی شیتها غیرفعال و یا به عبارتی غیرقابل ویرایش و فقط خواندنی باشه.
برای این مورد به این لینک مراجعه کنید
دوره تعیین سطح دسترسی در فایل های اکسل
سلام
تشکر بابت آموزش های عالی شما
من در فایل اکسل قصد دارم در Table فقط سلولهای که مدنظر هست رو قفل کنم ، با روشهای قفل کردن که شما آموزش دادید وقتی این کار رو انجام میدم دیگه table از حالت پویا خارج میشه یعنی دیگه با زدن tab ردیف جدید برام ایجاد نمیشه و بنوعی خاصیت table از بین میره .
میشه لطفا راهنمائی بفرمائید
سلام من کدی میخواام که وقتی داخل یک سلول متنی نوشتیم اون رو قفل کنه و به بقیه شیت کاری نداشته باشه البته کل شیت قفل هست ولی سلولی که باز هست ماربر اسم رو وارد میکنه دیگه نتونه تغییری بهش بده
سلام راجع به این موضوع توی اولین آپدیت این مطلب صحبت خواهیم کرد
باسلام
می خواهم در یک اکسل در سلولی اگر ۱ ثبت شد چند سلول مورد نظر فعال و چند سلول دیگر غیر فعال و اگر صفر ثبت شد بلعکس ممنون می شم راهنمایی کنید
این کار به راحتی توسط کد نویسی قابل انجام است .
در اولین فرصت در قالب یک ویدئو به آن پاسخ خواهم داد .
سلام چطور میشه محتویات یک سلول را غیر فعال کرد، در فاکتور زنی میخوام استفاده کنم مثلا با انتخاب یک سلول فقط بخشی که میخوام ویرایش کنم انتخاب بشه برای تغییر