אחד מעמיתי שאל אותי אם אפשר לבצע מספר בחירות ברשימה הנפתחת ב- Excel.
כאשר אתה יוצר רשימה נפתחת, תוכל לבצע בחירה אחת בלבד. אם תבחר פריט אחר, הראשון יוחלף בבחירה החדשה.
הוא רצה לבצע בחירות מרובות מאותו נפתח באופן שהסלקציות יתווספו לערך שכבר קיים בתא.
משהו כפי שמוצג למטה בתמונה:
אין שום דרך שתוכל לעשות זאת בעזרת תכונות מובנות של Excel.
הדרך היחידה היא להשתמש בקוד VBA, הפועל בכל פעם שאתה מבצע ומוסיף את הערך שנבחר לערך הקיים.
צפה בוידאו - כיצד לבחור פריטים מרובים מרשימה נפתחת של Excel
כיצד לבצע בחירות מרובות ברשימה נפתחת
במדריך זה אראה לך כיצד לבצע מספר בחירות ברשימה הנפתחת של Excel (עם חזרה וללא חזרה).
זו הייתה אחת ממדריכי האקסל הפופולריים ביותר באתר זה. מכיוון שאני מקבל הרבה שאלות דומות, החלטתי ליצור קטע שאלות נפוצות בסוף הדרכה זו. אז אם יש לך שאלות לאחר קריאת זה, אנא בדוק תחילה את סעיף השאלות הנפוצות.ישנם שני חלקים ליצירת רשימה נפתחת המאפשרת בחירות מרובות:
- יצירת הרשימה הנפתחת.
- הוספת קוד VBA לקצה האחורי.
יצירת הרשימה הנפתחת ב- Excel
להלן השלבים ליצירת רשימה נפתחת ב- Excel:
- בחר את התא או את טווח התאים שבו ברצונך להציג את הרשימה הנפתחת (C2 בדוגמה זו).
- עבור אל נתונים -> כלי נתונים -> אימות נתונים.
- בתיבת הדו -שיח אימות נתונים, בכרטיסיית ההגדרות, בחר 'רשימה' כקריטריוני אימות.
- בשדה מקור, בחר את התאים שבהם הפריטים שאתה רוצה בתפריט הנפתח.
- לחץ על אישור.
כעת, לתא C2 יש רשימה נפתחת המציגה את שמות הפריטים ב- A2: A6.
נכון לעכשיו, יש לנו רשימה נפתחת שבה ניתן לבחור פריט אחד בכל פעם (כפי שמוצג להלן).
כדי לאפשר נפתח זה לאפשר לנו לבצע מספר בחירות, עלינו להוסיף את קוד ה- VBA בקצה האחורי.
שני החלקים הבאים של הדרכה זו יתנו לך את קוד VBA כדי לאפשר בחירות מרובות ברשימה הנפתחת (עם וללא חזרה).
קוד VBA לאפשר בחירות מרובות ברשימה נפתחת (עם חזרה)
להלן קוד VBA של Excel שיאפשר לנו לבחור יותר מפריט אחד מהרשימה הנפתחת (המאפשר חזרות בבחירה):
תת פרטי גליון עבודה_שינוי (ByVal Target as Range) 'קוד לפי Sumit Bansal מ- https://trumpexcel.com' לביצוע בחירות מרובות ברשימה נפתחת ב- Excel Dim Oldvalue כמחרוזת Dim Newvalue כמחרוזת על שגיאה GoTo Exitsub If Target.Address = "$ C $ 2" ואז אם Target.SpecialCells (xlCellTypeAllValidation) אינו דבר אחר כך GoTo Exitsub Else: If Target.Value = "" ואז GoTo Exitsub Else Application.EnableEvents = False Newvalue = Target.Value Application.Undo Oldvalue = Target.Value אם Oldvalue = "" ואז Target.Value = Newvalue אחר Target.Value = Oldvalue & "," & Newvalue סוף אם סוף אם סוף אם Application.EnableEvents = True Exitsub: Application.EnableEvents = Sub End True
כעת עליך למקם קוד זה במודול בעורך VB (כפי שמוצג להלן בסעיף 'היכן לשים את קוד VBA').
לאחר שהצבת את הקוד הזה בקצה האחורי (יוסבר בהמשך הדרכה זו), הוא יאפשר לך לבצע מספר בחירות בתפריט הנפתח (כפי שמוצג להלן).
שים לב שאם תבחר פריט יותר מפעם אחת, הוא יוזן שוב (מותר לחזור על הפעולה).
נסה זאת בעצמך … הורד את קובץ הדוגמה
קוד VBA לאפשר בחירות מרובות ברשימה נפתחת (ללא חזרה)
הרבה אנשים שאלו לגבי הקוד לבחור מספר פריטים מתוך רשימה נפתחת ללא חזרה.
להלן הקוד שיוודא שניתן לבחור פריט פעם אחת בלבד, כך שלא יהיו חזרות:
תת פרטי גליון עבודה_שינוי (ByVal Target As Range) 'קוד לפי Sumit Bansal מ- https://trumpexcel.com' כדי לאפשר בחירות מרובות ברשימה נפתחת ב- Excel (ללא חזרה) Dim Oldvalue כמחרוזת Dim Newvalue כמחרוזת יישום. True On Error GoTo Exitsub If Target.Address = "$ C $ 2" ואז אם Target.SpecialCells (xlCellTypeAllValidation) אינו דבר אחר כך GoTo Exitsub אחר: אם Target.Value = "" ואז GoTo Exitsub Else Application.EnableEvents = False Newvalue = Target. Value Application.Undo Oldvalue = Target.Value If Oldvalue = "" ואז Target.Value = Newvalue אחר אם InStr (1, Oldvalue, Newvalue) = 0 ואז Target.Value = Oldvalue & "," & Newvalue אחר: Target.Value = Oldvalue סוף אם סוף אם סוף אם סוף אם סוף אם Application.EnableEvents = True Exitsub: Application.EnableEvents = Sub End True
כעת עליך למקם קוד זה במודול בעורך VB (כפי שמוצג בחלק הבא של מדריך זה).
קוד זה יאפשר לך לבחור מספר פריטים מהרשימה הנפתחת. עם זאת, תוכל לבחור פריט רק פעם אחת. אם תנסה לבחור אותו שוב, שום דבר לא יקרה (כפי שמוצג להלן).
נסה זאת בעצמך … הורד את קובץ הדוגמה
היכן לשים את קוד ה- VBA
לפני שתתחיל להשתמש בקוד זה באקסל, עליך לשים אותו בקצה האחורי, כך שיופעל בכל פעם שיש שינוי כלשהו בבחירה הנפתחת.
בצע את השלבים שלהלן כדי לשים את קוד VBA בקצה האחורי של Excel:
- עבור לכרטיסייה מפתחים ולחץ על Visual Basic (אתה יכול גם להשתמש בקיצור המקשים - Alt + F11). פעולה זו תפתח את עורך Visual Basic.
- אמורה להיות חלונית סייר פרויקטים בצד שמאל (אם היא לא נמצאת שם, השתמש ב- Control + R כדי להפוך אותה לגלויה).
- לחץ פעמיים על שם גליון העבודה (בחלונית השמאלית) שבו נמצאת הרשימה הנפתחת. פעולה זו פותחת את חלון הקוד עבור גליון העבודה.
- בחלון הקוד, העתק והדבק את הקוד לעיל.
- סגור את עורך VB.
כעת, כאשר תחזור לתפריט הנפתח ותבצע בחירות, הוא יאפשר לך לבצע מספר בחירות (כפי שמוצג להלן):
נסה זאת בעצמך … הורד את קובץ הדוגמה
הערה: מכיוון שאנו משתמשים בקוד VBA כדי לבצע זאת, עליך לשמור את חוברת העבודה עם סיומת .xls או .xlsm.
שאלות נפוצות (שאלות נפוצות)
יצרתי את החלק הזה כדי לענות על כמה מהשאלות הנפוצות ביותר בנושא הדרכה זו וקוד ה- VBA. אם יש לך שאלות, אני מבקש ממך לעבור ברשימת השאילתות הזו תחילה.
ש: בקוד VBA, הפונקציונליות מיועדת לתא C2 בלבד. איך אני משיג את זה לתאים אחרים? תשובות: כדי לקבל את התפריט הנפתח של בחירה מרובה זו בתאים אחרים, עליך לשנות את קוד ה- VBA בממשק האחורי. נניח שאתה רוצה לקבל את זה עבור C2, C3 ו- C4, עליך להחליף את השורה הבאה בקוד: אם Target.Address = "$ C $ 2" ואז עם השורה הזו: If Target.Address = "$ C $ 2" או Target.Address = "$ C $ 3" או Target.Address = "$ C $ 4" לאחר מכן
ש: אני צריך ליצור מספר נפתח-עמדות בכל העמודה 'C'. כיצד אוכל להשיג זאת עבור כל התאים בעמודות עם פונקציונליות מרובת בחירות? תשובות: כדי לאפשר בחירות מרובות בתפריטים הנפתחים בעמודה שלמה, החלף את השורה הבאה בקוד: אם Target.Address = "$ C $ 2" ואז בשורה זו: אם Target.Column = 3 ואז בשורות דומות, אם אתה רוצה פונקציונליות זו בעמודה C ו- D, השתמש בשורה הבאה: אם Target.Column = 3 או Target.Column = 4 לאחר מכן
ש: אני צריך ליצור מספר נפתח ברציפות. איך אני יכול לעשות את זה? תשובות: אם אתה צריך ליצור רשימות נפתחות עם מספר בחירות בשורה (נניח את השורה השנייה), עליך להחליף את שורת הקוד שלהלן: אם Target.Address = "$ C $ 2" ואז בשורה זו: אם Target.Row = 2 ואז באופן דומה, אם אתה רוצה שזה יעבוד עבור מספר שורות (נניח בשורה השנייה והשלישית), השתמש במקום זאת בשורת הקוד הבאה: אם Target.Row = 2 או Target.Row = 3 לאחר מכן
ש: נכון לעכשיו, הבחירות המרובות מופרדות בפסיק. כיצד אוכל לשנות זאת כדי להפריד בין רווח (או כל מפריד אחר). תשובות: כדי להפריד בין אלה במפריד שאינו פסיק, עליך להחליף את השורה הבאה של קוד VBA: Target.Value = Oldvalue & "," & Newvalue בשורה זו של קוד VBA: Target.Value = Oldvalue & "" & Newvalue באופן דומה, אם ברצונך לשנות פסיק עם תו אחר, כגון |, תוכל להשתמש בשורת הקוד הבאה: Target.Value = Oldvalue & "|" & Newvalue
ש: האם אוכל לקבל כל בחירה בשורה נפרדת באותו תא? תשובה: כן אתה יכול. כדי לקבל זאת, עליך להחליף את השורה הבאה של קוד VBA: Target.Value = Oldvalue & "," & Newvalue בשורת קוד זו: Target.Value = Oldvalue & vbNewLine & Newvalue vbNewLine מוסיף שורה חדשה באותו תא . כך שבכל פעם שתבחר בחירה מהתפריט הנפתח, הוא יוכנס בשורה חדשה.
ש: האם אוכל לגרום לפונקציונליות הבחירה המרובה לעבוד בגיליון מוגן? תשובה: כן אתה יכול. כדי לעשות זאת, עליך לעשות שני דברים: הוסף את השורה הבאה בקוד (מיד אחרי משפט DIM): Me.Protect UserInterfaceOnly: = נכון שנית, עליך לוודא שהתאים - בעלי התפריט הנפתח עם פונקציונליות בחירה מרובה - אינם נעולים כאשר אתה מגן על כל הגיליון. להלן הדרכה כיצד לעשות זאת: נעילת תאים ב- Excel
