สร้างรายการหล่นหลายชั้นที่ขึ้นกันได้ใน Excel VBA – 3 วิธีง่ายๆ

การสร้างรายการหล่นหลายชั้นที่ขึ้นกันได้ (dependent drop-down lists) เสมอมาท้าทายใน MS Excel เราต้องการรายการหล่นแบบนี้เมื่อมีความขึ้นกันหรือความสัมพันธ์ระหว่างสองรายการหล่น เราสร้างแบบนี้โดยทั่วไปด้วย สูตรต่างๆ หรือเปลี่ยนตัวเลือกที่เหมาะสมใน Excel อย่างไรก็ตาม มีวิธีง่ายๆ ในการสร้างรายการหล่นหลายรายการโดยใช้โค้ด Excel VBA ในบทความนี้ ผมจะแสดงวิธีต่างๆ ในการสร้างรายการหล่นหลายรายการโดยใช้โค้ด VBA ใน Excel

อ่านเพิ่มเติม: วิธีสร้างรายการหล่นใน Excel (อิสระและขึ้นกันได้)

รายการหล่นที่ขึ้นกันได้ (Dependent Drop-Down Lists) ใน Excel คืออะไร?

ก่อนไปสู่ขั้นตอนหลัก มาทำความเข้าใจว่ารายการหล่นที่ขึ้นกันได้ใน Excel คืออะไร เมื่อมีความขึ้นกันระหว่างรายการหล่นสองรายการขึ้นไป เราเรียกว่ารายการหล่นที่ขึ้นกันได้ใน Excel ภาพด้านล่างแสดงแนวคิดที่ชัดเจน

เห็นได้ว่ามีรายการหล่นสองรายการ คือ Category และ Food เป็นรายการหล่นที่ขึ้นกันได้สมบูรณ์ ขึ้นอยู่กับการเลือก Category เรากำหนดรายการอาหารที่นี่ ดังนั้น หลายรายการหล่นแบบคASCADE ทำงานอย่างไร

อ่านเพิ่มเติม: วิธีสร้าง Dynamic Dependent Drop Down List ใน Excel

3 วิธี สร้างรายการหล่นหลายชั้นที่ขึ้นกันได้ Excel VBA

1. วิธีเลือกหลายรายการในรายการหล่น Excel VBA

สมมติว่าเรามีสองรายการชื่อ Project Name และ Project Members สำหรับแต่ละโครงการ เราจะมอบสมาชิกหนึ่งหรือหลายคนโดยใช้รายการหล่น

ขั้นตอนที่ 1: ไปที่แท็บ Developer เปิด Visual Basic (Shortcut Alt + F11)

ขั้นตอนที่ 2: ไปที่ชีตงานที่เกี่ยวข้องจากเมนู VBAProject ที่เกี่ยวข้อง

ขั้นตอนที่ 3: เขียนโค้ดต่อไปนี้ในคอนโซล VBA

โค้ด:

Private Sub Worksheet_Change(ByVal Target As Range) Dim Old_value As String Dim New_value As String Application.EnableEvents = True On Error GoTo Exitsub If Not Intersect(Target, Range("C4:C11")) Is Nothing Then If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then GoTo Exitsub Else: If Target.Value = "" Then GoTo Exitsub Else Application.EnableEvents = False New_value = Target.Value Application.Undo Old_value = Target.Value If Old_value = "" Then Target.Value = New_value Else If InStr(1, Old_value, New_value) = 0 Then Target.Value = Old_value & ", " & New_value Else: Target.Value = Old_value End If End If End If End If Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub

ขั้นตอนที่ 4: เลือกหลายชื่อในคอลัมน์ Project Member

ขั้นตอนที่ 5: เซลล์ทั้งหมดสามารถเลือกหลายรายการจากรายการหล่นได้

อ่านเพิ่มเติม: วิธีเลือกหลายรายการจาก Drop Down List ใน Excel

2. สร้างรายการหล่นหลายชั้นที่ขึ้นกันได้ใน Excel VBA

สมมติว่ามีชุดข้อมูลอาหารประเภทต่างๆ เช่น ผัก ผลไม้ และผลิตภัณฑ์นม ตอนนี้เราต้องการค้นหาอาหารตามประเภท เช่น ถ้าเลือก Category เป็น Fruits ในคอลัมน์ Food รายการที่มีควรเป็น Raspberry, Apricot, Peach, Mango รายการอาหารควรพร้อมใช้งานตาม Category มีความขึ้นกันระหว่าง Category และ Food

ขั้นตอนที่ 1: เปิดคอนโซล VBA ตามขั้นตอนวิธี 1 (ขั้นตอนที่ 1 และ ขั้นตอนที่ 2) แล้วเขียนโค้ดต่อไปนี้

โค้ด:

สำหรับสร้างรายการหล่นผัก:

Sub Vegetable_List() Range("C4:C6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ Formula1:="=Vegetable_List" End Sub

สำหรับสร้างรายการหล่นผลไม้:

Sub Fruit_List() Range("C4:C6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ Formula1:="=Fruits_list" End Sub

สำหรับสร้างรายการหล่นผลิตภัณฑ์นม:

Sub Dairy_List() Range("C4:C6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ Formula1:="=Dairy_Product_List" End Sub

ในส่วนนี้ เราสร้างรายการรายการอาหารแต่ละรายการและบันทึกไว้ในรายการหล่น รายการนี้จะพร้อมใช้งานในช่วง C4:C6

ขั้นตอนที่ 2: ตอนนี้เราต้องเขียนฟังก์ชันหลักสำหรับช่วง B4:B6

โค้ด:

Private Sub Worksheet_Change(ByVal Target As Range) Range("B4:B6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ Formula1:="Vegetable_List,Fruits_list,Dairy_Product_List" If Range("B4:B6").Value = "Vegetable_List" Then Call Vegetable_List ElseIf Range("B4:B6").Value = "Fruits_list" Then Call Fruit_List ElseIf Range("B4:B6").Value = "Dairy_Product_List" Then Call Dairy_List Else End If

คำอธิบายโค้ด

• เราสร้างรายการอีกอันสำหรับหมวดหมู่ในช่วง B4:B6 ซึ่งจะมีชื่อหมวดหมู่อาหาร
• จากนั้นตรวจสอบค่าของรายการและจัดหมวดหมู่ตามรายการของพวกเขา สำหรับนี้ใช้คำสั่ง IF ELSE
• หากพบชื่อที่ตรงกัน เราเพียงแค่เรียกฟังก์ชันสร้างรายการโดยใช้วิธี CallBack เช่น

If Range(“B4:B6”).Value = “Vegetable_List” Then

Call Vegetable_List

• ที่นี่ หากค่าเซลล์ตรงกับข้อความ Vegetable_List เราจะเรียกฟังก์ชัน Vegetable_List เพื่อสร้างและแสดงรายการผัก

ดังนั้น โค้ดเต็มของเราจะเป็นแบบนี้:

ขั้นตอนที่ 3: ไปที่ชีตงาน และเลือกหมวดหมู่จากรายการหล่น

ขั้นตอนที่ 4: รายการที่เกี่ยวข้องจะพร้อมใช้งานในคอลัมน์ Food

ขั้นตอนที่ 5: ผลลัพธ์สุดท้ายจะเป็นแบบนี้:

3. ล้างรายการหล่นหลายชั้นที่ขึ้นกันได้ใน Excel VBA

ในส่วนก่อนหน้า เราเห็นแค่วิธีได้รายการที่ตรงกันใน Excel แต่บางครั้งอาจมีการเลือกที่ไม่ตรงกัน ซึ่งจะไม่ถูกลบอัตโนมัติ เราสามารถใช้สูตรป้องกันปัญหานี้ได้

อีกตัวเลือกคือใช้แมโคร เพื่อล้างเซลล์ที่ขึ้นกัน หลังจากเลือกในรายการหล่นแรก นั่นจะป้องกันการเลือกที่ไม่ตรงกัน

ขั้นตอนที่ 1: เปิดคอนโซล VBA ตามวิธี 1 (ขั้นตอนที่ 1 และ ขั้นตอนที่ 2) แล้วเขียนโค้ดต่อไปนี้

โค้ด:

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Column = 2 Then If Target.Validation.Type = 3 Then Application.EnableEvents = False Target.Offset(0, 1).ClearContents End If End If exitHandler: Application.EnableEvents = True Exit Sub End Sub

ขั้นตอนที่ 2: เลือกรายการจากคอลัมน์ Food และลองเลือกหมวดหมู่ที่ต่างกันจาก Category ดูว่าจะเกิดอะไรขึ้น

First

Second

Final Output

อ่านเพิ่มเติม: วิธีลบ Drop-Down List ใน Excel

สิ่งที่ควรจำ

สรุป

นี่คือบางวิธีในการสร้างหรือจัดการรายการหล่นหลายชั้นที่ขึ้นกันได้ Excel VBA ผมแสดงทุกวิธีพร้อมตัวอย่าง แต่ยังมีการทำซ้ำอื่นๆ อาจมากกว่านี้ ผมยังอภิปรายพื้นฐานของฟังก์ชันที่ใช้ หากคุณมีวิธีอื่นในการทำสิ่งนี้ โปรดแบ่งปันกับเราได้เลย

การอ่านเพิ่มเติม

• วิธีสร้าง Drop Down List ในหลายคอลัมน์ใน Excel (3 วิธี)
• Excel Drop Down List Depending on Selection
• วิธีใช้ IF Statement สร้าง Drop-Down List ใน Excel
• สร้าง Drop Down List จากชีตอื่นใน Excel (2 วิธี)
• วิธีแก้ไข Drop-Down List ใน Excel (4 วิธีพื้นฐาน)
• VLOOKUP with Drop Down List in Excel