חסימת הזנת ערך באקסל לפי תנאי

[ל13]

חסימת הזנת ערך באקסל לפי תנאי

הי חברים,

צריך את עזרתכם...
האם אתם מכירים דרך פשוטה לחסום/למנוע הזנה של ערך מסוים (בחירה באימות נתונים) לפי תנאי שמוגדר בתא שלידו - נוסחה שעושה איזשהו חישוב ונותנת הודעה על חריגה במידה שהערך הזה יוזן?
כלומר, המשתמש מקבל התרעה על הזנה בעייתית של ערך, אבל ההזנה אינה נחסמת/נמנעת... אני צריך שבנוסף להתרעה המתקבלת בנוסחה שבתא הסמוך, המשתמש שיבחר את הערך הבעייתי לא יוכל להזין אותו בפועל ויקבל איכשהו הודעה מיידית(!) על הזנה שגויה שצריך לתקן?

*** אציין כי אין לי אפשרות להשתמש בחסימה הפשוטה של אימות נתונים, כי לא מדובר בניסיון להקליד נתון שלא ברשימה...

האם אפשר למצוא פיתרון באמצעות איזושהי נוסחה חכמה באימות הנתונים שתתייחס לתנאי בתא הסמוך ותחסום את ההזנה במקרה של חריגה?

האם ניתן למצוא פיתרון ע"י איזהו קוד VBA, אבל שיחסום מיידית, ולא ע"י הפעלת המאקרו?

מצ"ב קובץ לדוגמא.

תודה מראש

 

[u333]

אני, לא מבין כלום. (וכנראה לא רק אני...)

 

[ל13]

דווקא לי נראה שכתבתי הכי ברור... אבל אנסה להסביר שוב...

בקובץ המצורף:
- בטבלה משמאל - פירוט שעות לעובד לשבוע עבודה לפי ימים.
- בטבלה מימין - סיכום שעות שבועי לעובד.

עמודות H ו-M מסתכלות על שעות העבודה המוזנות ונותנות אתרעה במקרה של חריגה מ-60 שעות עבודה בשבוע. האתרעה מתקבלת בדיוק כאשר מוזן הנתון שמביא אותנו לחריגה הזו.
דוגמא: הזנת כבר 60 שעות לעובד מסוים. ניסית להזין לו אפילו שעה אחת נוספת,
קיבלת אתרעה על חריגה.

השאלה היא האם ישנה דרך שמעבר לאתרעה המתקבלת בעמודות H ו-M, ההזנה הגורמת לחריגה תיחסם (!), כלומר המשתמש לא יצליח להזין את הנתון ויקבל הודעה על חריגה, ורק שיתקן, ההזנה תתקבל?

תודה.

 

[u333]

כעת נראה לי יותר ברור - הצעתי - באמצעות מקרו אירוע

הכנס את הקוד הבא לגיליון plan ברמת הגיליון
נסה לשנות נתונים בעמודה F וראה מה קורה.
ליתר הבהרה - צירפתי את הקובץ שלך עם המקרו בפנים.

Private Sub Worksheet_Change(ByVal Target As Range)
Set Rng = Range("L5:L9")
    For Each cell In Rng
        If cell > 60 Then
            MsgBox "חרגת מכמות השעות"
            Application.Undo
        End If
    Next
End Sub

 

[ל13]

תודה רבה. אשתמש בזה!

 

[ל13]

ורק שאלה לגבי הפיתרון המעולה (!) שהצעת...

אני מנסה להטמיע את הפיתרון המעולה שהצעת, על קובץ המקור שלי,
שהוא קצת הרבה יותר מורכב מקובץ הדוגמא, וזה קצת בעייתי... כי יש לי סט שלם של תנאים מורכבים ואתרעות תואמות (לא רק חריגה מ-60 שעות), שעבור כל חריגה מתנאי מסוים מתקבלת האתרעה המתאימה לאותה החריגה.
לצורך ההבהרה, כפי שניתן לראות בקובץ הדוגמא, הזנת נתון בעמודה F, גורמת לקבלת חיווי (טקסטואלי) בעמודה H על ההזנה, אם תקינה או לא, שהוא תוצאה של נוסחה מאוד ארוכה ומורכבת שכוללת את כל סט התנאים.
לא חרגת משום תנאי - קיבלת ב-H את המלל 'OK'.
כן חרגת מאחד התנאים - קיבלת ב-H את האתרעה הייחודית על החריגה הרלוונטית.
האם יש דרך להטמיע את הפיתרון שלך אבל שחסימת ההזנה תתבסס על החיווי הטקסטואלי המתקבל ע"י נוסחה בעמודה H עבור כל תא המוזן בעמודה F?
כלומר אם התקבל 'OK' בעמודה H, אז זה תקין. קבל את ההזנה.
אבל אם התקבלה איזושהי אתרעה בעמודה H (למשל: חרגת מ-60 שעות), אז תחסום את ההזנה וב- Message Box שנפתח ע"י מאקרו האירוע יהיה רשום את אותו טקסט אתרעה שהתקבל ב-H.
מקווה שזה מובן...
תודה.

 

[u333]

ושוב, אם הבנתי נכון, נסה את הקוד הבא במקרו האירוע.

Private Sub Worksheet_Change(ByVal Target As Range)
Set Rng = Range("L5:L9")
    For Each cell In Rng
        If cell > 60 Then
           nr = ActiveCell.Row()
           MsgBox Cells(nr, 8)
           Application.Undo
        End If
    Next
End Sub

 

[ל13]

לגבי ההודעה - מצוין! לגבי התנאי - לא מה שהתכוונתי...

התכוונתי שהמאקרו אירוע לא יכיל את התנאי של Cell>60 ותנאים דומים,
אלא "יסיק" מתוך תוצאת הנוסחה בעמודה H איך לפעול... אם לקבל את ההזנה,
או לחסום אותה ולהציג אתרעה... כלומר:
כשעמודה H מציגה את המילה OK - הזנה מתקבלת.
כשעמודה H מציגה איזשהו סוג של Exceeded (עם רקע אדום) - הזנה לא תתקבל (תיחסם) ותופיע האתרעה עם סוג ה-Exeeded שהתקבל, בהודעת המאקרו (60< וכו').
הצלחתי להסביר?
תודה.

 

[u333]

חבל, חבל שאינך מנסה "לצאת מן הקופסה"...

נסה את הקוד הבא:
כמובן, תתעלם מן העברית המשובשת !

Private Sub Worksheet_Change(ByVal Target As Range)
Set Rng = Range("H5:H33")
    For Each cell In Rng
        If cell = "Exceeded!!!" Then
            MsgBox "çøâú îëîåú äùòåú"
            Application.Undo
        End If
    Next
End Sub

 

[ל13]

פשוט משחשבתי... תודה רבה!

 

[ל13]

יש רק בעיה קטנה...

בכל מקרה של חריגה, המאקרו מונע את ההזנה ומקפיץ הודעה כפי שביקשתי,
רק שזה נכנס ללופ אין סופי שאני לא מצליח לצאת ממנו...
חריגה --> הודעה על חריגה --> OK --> שוב קופצת הודעה על חריגה --> OK
וכך הלאה בלופ אין סופי.. לא מאפשר לי לתקן את ההזנה החורגת...
מה יכולה להיות הסיבה?

 

[u333]

צרף לכאן את הקובץ עם המקרו !

 

[ל13]

בדקתי... המאקרו תקין, רק ש...

כבר היו כמה חריגות קיימות בגיליון לפני שהפעלתי את המאקרו עליו,
כך שברגע שהפעלתי אותו וניסיתי לתקן הזנות שגרמו לחריגה,
אז המאקרו נכנס ללופ אינסופי עם הודעות חריגה חוזרות שלא מאפשרות לתקן הזנות ולא לעשות שום דבר עם הקובץ...

במקרה שיש בקובץ הזנה ישנה שגרמה לחריגה, האם יש דרך לאפשר את תיקונה ללא כניסה ללופ שתוקע את הקובץ?

מצ"ב הקובץ עם הזנות חורגות שנעשו לפני הפעלת המאקרו על הגיליון.
אין אפשרות לתקן אותן כי המאקרו מונע זאת...

תודה.

 

[u333]

לניטרול זמני של מקרו האירוע

פתח את הקובץ.
אל תיגע בשום דבר אל תעשה בו שום שינוי.
הכנס לעורך המקרו, ובקוד של גיליון Plan תוסיף לפני כל שורה גרש בודד.
כעת מקרו האירוע מנוטרל.
עשה את השינויים הדרושים. לאחר שתסיים - גש למקרו וסלק את כל הגרשים.
בדוק האם פועל ?

'Private Sub Worksheet_Change(ByVal Target As Range)
'Set Rng = Range("L5:L9")
'    For Each cell In Rng
'        If cell > 60 Then
'           nr = ActiveCell.Row()
'           MsgBox Cells(nr, 8)
'           Application.Undo
'        End If
'    Next
'End Sub

 

[ל13]

תודה, אני יודע איך לנטרל מאקרו זמנית. ניטרלתי.זו לא הבעיה...

אני שואל אם יש דרך לגרום למאקרו הנוכחי לאפשר תיקון של הזנות חורגות ישנות
ולחסום רק הזנות חורגות חדשות...

 

[ל13]

תוכל בבקשה להסביר את הקטע הבא...?

לא הבנתי מה זה nr ולמה 8?​

nr = ActiveCell.Row()
MsgBox Cells(nr, 8)
​

תודה.

 

[u333]

חשבתי שיש לך ידע בסיסי ב-VBA

nr - משתנה המקבל את הערך של מס. השורה בה נמצא הסמן (התא הפעיל)
ואז, בתיבת ההודעה אתה מקבל את תכולת התא בעמודה 8 - היא עמודה H - ושורה nr - שהיא השורה בה אתה נמצא.

 

[ל13]

תודה רבה!