באחת התגובות בפוסט הקודם שלי בנושא רשומות כפולות ב-Excel שואלת אותי יעל "האם ניתן למנוע רשומות כפולות גם ברשימה שהגדרתי כרשימה נפתחת ?"
התשובה היא כן, אבל הפתרון אינו טריוויאלי. יעל מבקשת להפעיל שני סוגים שונים של אימות נתונים:
1 – אימות נתונים – מותאם אישית-נוסחה, כפי שהראינו בפוסט הקודם, על מנת למנוע הכנסה של רשומה שכבר קיימת בטווח;
2 – אימות נתונים-רשימה, על מנת ליצור רשימה נפתחת של ערכים שמוצר לרשום בטווח.
Excel לא מאפשרת להפעיל שני סוגים שונים של אימות נתונים על אותו תא אבל אפשר להתגבר על המגבלה בשתי גישות שונות: פתרון עם מקרוס ופתרון עם נוסחאות. בפוסט זה אציג את הפתרון עם מקרוס.
נניח את המצב הזה
בטווח C2:C8 יצרנו טבלה (Table) ובה תאים שלה יצרנו רשימה נפתחת שניזונה מהערכים שבטווח A3:A14. על מנת להקל על העבודה שלנו, יצרנו שם name)(("List_of_Names" שמתייחס לטווח A3:A14; ושינינו את השם של הטבלה בטווח C2:C8 ל"tblWorkers".
כל פעם שנפתח את השימה המתגלגלת נראה את כל השמות. לא נוכל למנוע את הכנסתו של שם שכבר מופיע בטבלה אבל באמצעות event (מקרו) נוכל להודיע למשתמש שהשם כבר קיים ולמחוק אותו מהתא.
ה-events הם סוג מיוחד של מקרוס שמופעלות כאשר מתרחש אירוע מסוים בגיליון. הקוד של ה-event יש לכתוב במודול של הגיליון הרלוונטי ולא במודול כללי של העורך Vbe.
על מנת לגשת של מודול של הגיליון מצביעים על לשונית השם של הגיליון, לוחצים על כפתור ימין של העכבר ובוחרים באופציה "הצג קוד"
במודל של הגילייון כותבים את הקוד הזה
1 2 3 4 5 6 7 8 9 10 11 12 | (Private Sub Worksheet_Change(ByVal Target As Range Dim bcheckDup As Integer If Union(Target, Range("tblWorkers")).Address = Range("tblWorkers").Address Then Application.EnableEvents = False (bcheckDup = WorksheetFunction.CountIf(Range("tblWorkers"), Target If bcheckDup > 1 Then "השם כבר מופיע בטבלה" MsgBox Target.ClearContents End If Application.EnableEvents = True End If End Sub |
ה-event מופעל כאשר מתרחש שינוי באיד התאים בגיליון (Worksheet_Change). בצעד הראשון הקוד בודק האם השינוי התרחש באחד התאים של הטבלה; אם השינוי היה באחד התאים של הטבלה הקוד בודק האם השם כבר מופיע באמצעות הפונקציה COUNTIF; עם התוצאה של COUNTIF גדולה מ-1, השם כבר קיים והקוד יוצר הודעה (MsgBox) ומוחק את הערך שהוכנס.
אפשר להסתיר את עמודת A.
הסירטון הבא מדגים את הטכניקה
בפוסט הבא נדגים פתרון באמצעות נוסחאות.
