راهکار VBA اکسل برای حفاظت از شیت؛ بدون قفل‌کردن شیت

در اکسل با استفاده از بخش ایونت‌های VBA به‌سادگی می‌توانیم بدون قفل‌کردن شیت، جلوی تغییر رنج انتخابی را بگیریم.

مشکل VBA با تغییردادن رنج‌های قفل‌شده

گمانم این تشبیه خوبی باشد اگر بگوییم قفل‌کردن خانه‌های (Cells) شیت اکسل (Protect Sheet) مانند این است که روی آن‌ها شیشه بگذاریم. در این تشبیه، فرمول‌نویسی از زیر شیشه کار می‌کند و ماکرونویسی (برنامه‌نویسی VBA) از روی شیشه. برای همین است که رنج‌های (Renge) قفل‌شده را نمی‌شود با VBA تغییر داد؛ مگر اینکه هر بار قفلش را باز و بسته کنیم.

زمانی فکرم مشغول این مسئله بود که می‌خواهم برخی خانه‌ها را با VBA تغییر بدهم ولی کاربر نتواند تغییرش بدهد. اگر قفل کنم، VBA کار نمی‌کند؛ اگر نکنم، کاربر ممکن است دستکاریش کند. چه کار می‌شود کرد؟

برای نمونه، همیشه در بالای شیت‌هایم چند خانه برای اعلام خطای برنامه دارم. برنامه پس از اجرای فرمان‌ها در آنجا پیام متنی می‌نویسد و به کاربر چیزی را که باید بداند می‌گوید. چنین خانه‌هایی (Cells) اگر قفل باشند، VBA نمی‌تواند رویشان بنویسد.

راهکار منطقی مامان‌ها!

بالاخره یاد نکته‌ای منطقی افتادم. اینکه کار با کامپیوتر سه مرحلهٔ کلی دارد: (۱) انتخاب چیز، (۲) انتخاب دستور، (۳) اجرای فرمان. بعد فکر کردم اگر بشود جلوی کاربر را بگیرم که نتواند «چیز» را که در اینجا یک یا چند خانهٔ اکسل است انتخاب کند، یا نتواند از مرحلهٔ اول جلوتر برود، در عمل نمی‌تواند تغییر بدهد و به خواسته‌ام رسیده‌ام.

در اکسل می‌توان در ایونت Worksheet_SelectionChange با انتخاب‌کردن خانه‌ها کار کرد. پس فکر کردم که در این ایونت می‌توانم به اکسل بگویم کاربر به کدام خانه‌ها دست نزند. سرانجام موفق شدم همین را بکنم البته بدین شکل که گفتم به هیچ خانه‌ای دست نزند، مگر این استثناها که مشخص می‌کنم.

سرانجام اجرایش کار ساده‌ای از آب درآمد؛ مانند کاری شد که مامان‌ها برای جلوگیری از ناخنک‌زدن به سیب‌زمین سرخ‌کرده می‌کنند: پشت‌دستی! وقتی کاربر جایی از شیت را انتخاب می‌کند، SelectionChange تغییر می‌کند. آنگاه ایونت به کار می‌افتد و نگاه می‌کند اگر انتخاب در رنج غیرمُجاز باشد، بلافاصله Selection را تغییر می‌دهد و خانه‌ای را انتخاب می‌کند که در بخش مجاز است. بدین ترتیب کاربر نمی‌تواند انتخابش را نگه دارد تا به مرحلهٔ بعدی یعنی فرمان‌دادن برسد. همین که بخش غیرمجاز را انتخاب می‌کند می‌پرد یک جای دیگر.

اجرای راهکار

بخش اول: متد Intersect

بار اول فکر کردم برای اینکه بتوانم بررسی کنم رنجی که کاربر انتخاب کرده با رنج غیرمجاز هم‌پوشانی دارد یا خیر، یک متد بنویسم که دو رنج را بگیرد، اگر هم‌پوشانی داشتند True بدهد وگرنه False بدهد. شروع کردم به فکرکردن به حالت‌های گوناگون هم‌پوشانیِ دو رنج و کد نوشتم و نوشتم و نوشتم... . دیدم این که تمام نمی‌شود! پس رفتم سراغ اینترنت و جستجو کردم تا متد آمادهٔ Intersect را پیدا کردم.

این‌طور که یاد گرفتم، کارکردن با Intersect بدین شکل است که باید یک آبجکت بسازیم و مقدار متد را داخل آن بریزیم. بعد برایش شرط بنویسیم که اگر جوابش هیچ (Is Nothing) بود False بدهد وگرنه True. بدین شکل:

Dim ResultTargetInRange As Boolean
Set TargetInRange = Application.Intersect(Range(Target), Range(RangeNamge))
If TargetInTable Is Nothing Then
       ResultTargetInRange = False
Else
        ResultTargetInRange= True
End If

دیدم به این شکل استفاده‌کردن سخت است. بردمش داخل متد دست‌ساز خودم که کوتاه باشد؛ فقط دو رنج را بگیرد و True/False بدهد.

بخش دوم: اجرای متد در ایونت Worksheet_SelectionChange

حالا باید متد را در ایونت SelectionChange اجرا کرد. این دیگر برای آن‌هایی که بلدند شرط بنویسند، مانند آب خوردن است! برای این کار اول باید بدانید کجاها منطقهٔ مُجاز است. سپس ویژگی هریک را از نظر منطق شرط‌چیدن و از نظر منطق رفتار برنامه در نظر بگیرید و شرط چندبخشی را بنویسید. ضمنا می‌شود برای جاهایی خاصی در رنج غیرمجاز، رفتار جداگانه تعریف کرد.

در کد زیر برای آسان‌بودن کار، پراپرتی‌ای از نوع Get با نام StartPoint تعریف کرده‌ام که نشانی خانه‌ای که با انتخاب بخش‌های غیرمجاز انتخاب می‌شود در آن است. Tools نام کلاس برنامه است که پراپرتی از آنجا فراخوانی شده.

در شرط اول می‌گوید اگر خانهٔ A3 را انتخاب کرده بود، همان جا بماند. در شرط دوم می‌گوید اگر Target یعنی رنجی که کاربر انتخاب کرده، درون دو جدول داخل شیت نباشد به خانهٔ StartPoint برود. برای آسانی کار، متد EventRangeControl2Table را نوشته‌ام که هم‌پوشانی Target را در دو رنج (جدول)، هم‌زمان بررسی می‌کند.

If Target.Address = Range(&quotA3&quot).Address Then
        Target.Select
ElseIf Tools.EventRangeControl2Table(Target, &quotFilterMS&quot, &quotMains&quot) = False Then
        Range(Tools.StartPoint).Select
End If

شرح تصویر: بخش‌های قرمز نمایش رنج محافظت‌شده، بخش‌های سبز نمایش رنج مجاز

خوانندهٔ محترمی که خودش استاد است یا خواننده‌ای که واردتر است، لطفا اگر نکته‌ای به‌نظرش رسید بفرماید.

شغل من کار با زبان فارسی است و به اکسل هم علاقه‌مندم.
لینکدین و توئیتر نویسنده.