מאקרו אירוע לשמירת הערך הקודם

מאמר נוסף בסדרת המאמרים על מאקרו אירוע, והפעם – איך שומרים את הערך הקודם שבתא?
לצורך הדוגמה, המשתמש צריך להזין בתא A1 נתון שמוגבל לערכים שבין 20 ל-30.
אם המידע אינו עומד בקריטריונים, ימחק הערך שכתב, ויוחזר הערך הקודם.

אז ראשית נכתוב את הקוד לבדיקת המידע.
מכיוון שמדובר בהזנת ערך, נבחר במאקרו אירוע מסוג Worksheet_Change שניצת ברגע שנעשה שינוי בגיליון:

אני מזכירה לכם שכדי להתייחס לתא שבו התרחש האירוע,
לא נשתמש ב ActiveCell, שהוא התא הנוכחי, אלא ב Target שהוא התא שהצית את האירוע.

מה ההבדל בין ActiveCell ל-Target ?

נניח שאנחנו כותבים בתא A1 ערך כלשהו ולוחצים על אנטר בסיום ההזנה.
התא שאמור להצית את האירוע הוא A1, והוא זה שנשמר ב Target,
התא הפעיל הוא A2, משום שלחצנו על Enter בסיום ההזנה, והוא זה שנשמר ב ActiveCell.

ונחזור להמשך הקוד:
אני מעוניינת להגביל את הערכים המוזנים לתא A1 למספרים שבין 20 ל-30.
כאן חשוב להבין שמאקרו אירוע מסוג Change יוצת תמיד כשיהיה שינוי בתא,
ולכן עלינו לבדוק בתחילת הקוד האם מדובר בתא שאותו אנחנו רוצים לבדוק.

If Target.Address = Range("a1").Address Then
End If

ואפשר לכתוב את הפקודה הזאת גם כך:

If Target.Address = "$A$1" Then 
End If

טיפ קטן לקבלת כתובת התא, ללא סימני ה-$:

If Target.Address(False, False) = "A1" Then
End if

עכשיו נוסיף את התנאי

If Target.Address = "$A$1" Then
   If Target < 20 Or Target > 30 Then
      Application.EnableEvents = False
      Target = ""
      Application.EnableEvents = True
   End If
End If

אז מה יש לנו פה?

ערכתי בדיקה האם הערך קטן מ-20 או גדול מ-30 (אלה המקרים שבהם צריך להחזיר את הערך הקודם שהיה בתא).
אם התנאי מתקיים, אני מבקשת למחוק את הערך שקיים ב Target.
אז מה משמעות הפקודה Application.EnableEvents?
מכיוון שמדובר במאקרו אירוע מסוג Change, שמופעל עם שינוי הערך בתא,
אז כאשר נשנה את הערך שהמשתמש הקליד לערך אחר (במקרה שלנו – נשנה את הערך שבתא ל'ריק'),
המאקרו יופעל שוב, כי הערך השתנה, ואנו עלולים להכנס ללולאה אינסופית שבה כל שינוי של התא מצית מחדש את האירוע.
לכן, לפני שאני משנה את ערך התא בקוד, אני מבקשת לכבות את המנגנון שמפעיל את האירועים.
כעת, כאשר אשנה את הערך שבתא, הוא לא יצית שוב את האירוע Change.
לאחר סיום הפעולה, אדליק את המנגנון חזרה, כך שהוא יפעל בפעם הבאה שאזין ערך לתא.

אבל רגע אחד…

לא אמרנו שבמקרה שבו המשתמש הזין ערך שלא נמצא בטווח שקבענו, נרצה להחזיר את הערך הקודם?
אז כדי לעשות את זה, אנחנו צריכים לשמור את הערך הזה במקום כלשהו.
הבעיה היא שאי אפשר לשמור אותו בקוד של האירוע Change, מכיוון שהוא מופעל אחרי שכבר הזנו את הערך החדש והערך הקודם לא קיים יותר.

אז מה הפתרון?

נוסיף אירוע מסוג SelectionChange, שמופעל בכל פעם שאנחנו בוחרים תא כלשהו, ונשמור את הערך בתוך משתנה.
אבל זה לא מספיק, כי כדי שהערך ישמר ביציאה מהקוד, עלינו להכריז עליו כ Public, אשר מוכר בכל המודול.
אז בעצם יש לנו כאן שני מאקרו אירוע שמופעלים: האחד אירוע בעת בחירה, ששומר לתוך משתנה את הערך הנוכחי,
והשני, מאקרו אירוע לאחר שינוי, שבודק האם הערך עומד בכללים שקבענו, ואם לא – מחזיר לתא את הערך הקודם,
שאותו שמרנו במאקרו אירוע בעת בחירה.

והנה הקוד המלא:

Public Tmp

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then

   If Target < 20 Or Target > 30 Then

      Application.EnableEvents = False

      Target = Tmp

      Application.EnableEvents = True

   End If

End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Tmp = Target

End Sub

אז מה היה לנו פה?

1. כתיבת מאקרו אירוע מסוג Change שבודק אם הנתון שהוזן לתא עומד בתנאים
2. כתיבת מאקרו אירוע מסוג SelectionChange ששומר את הערך הקיים בתא בעת הכניסה אליו (כלומר – לפני השינוי)
3. הכרזה על המשתנה Tmp כ Public, כדי שיוכר בכל המודול