สร้าง Excel Data Validation Drop-Down List รองรับการเลือกหลายรายการ

ในบทความนี้ เราจะเรียนรู้วิธีสร้าง excel data validation drop-down list ที่รองรับการเลือกหลายรายการ (multiple selection) ขณะที่เราใช้ data validation ปกติ จะอนุญาตให้เลือกได้เพียงรายการเดียวจากลิสต์ แต่ ถ้าเราต้องการเลือกหลายรายการจากเมนูดรอปดาวน์ของ data validation list จะทำอย่างไร ดังนั้น ในบทช่วยสอนนี้ เราจะใช้แมโครบางตัวเพื่อแก้ไขข้อจำกัดของการเลือกหลายรายการจาก data validation drop-down list

คุณสามารถดาวน์โหลดสมุดงานฝึกปฏิบัติได้จากที่นี่

3 ตัวอย่าง สร้าง Data Validation Drop-Down List รองรับ Multiple Selection ใน Excel

กระบวนการใช้ VBA macro เพื่อสร้าง data validation drop-down list ที่รองรับ multiple selections เป็นกระบวนการที่ค่อนข้างซับซ้อน ดังนั้น เพื่อให้เข้าใจง่ายขึ้น เราจะสาธิต 3 ตัวอย่างที่แตกต่างกันของการเลือกหลายรายการจาก data validation drop-down list

1. สร้าง Excel Data Validation Drop-Down List สำหรับ Multiple Selection ในเซลล์เดียว

ก่อนอื่น เรามีชุดข้อมูลต่อไปนี้ ซึ่งประกอบด้วยชื่อของ 4 ประเทศ เราต้องการสร้าง data validation drop-down list ที่รองรับการเลือกหลายรายการจากชื่อเหล่านี้ ปกติ data validation จะอนุญาตให้เลือกชื่อประเทศเดียวในเซลล์เดียว แต่ เราต้องการป้อนชื่อหลายประเทศจาก data validation list ในเซลล์เดียว

มา看看ขั้นตอนในการดำเนินการนี้

ขั้นตอน:

• ขั้นแรก เลือกช่วงเซลล์ (D4:D8) ตั้งชื่อช่วง (name range) เป็น 'dv_list_0'

• ขั้นที่สอง เลือกช่วงเซลล์ (B5:B8) และตั้งชื่อช่วงเป็น 'Country_Range'

• ขั้นที่สาม ไปที่ Data > Data Tools > Data Validation > Data Validation

• การกระทำข้างต้นจะเปิดกล่องโต้ตอบใหม่ชื่อ 'Data Validation'
• ต่อไป เลือกตัวเลือก List จาก Allow ใส่สูตรต่อไปนี้ในช่องข้อความ Source:

• คลิก OK

• ดังนั้น ไอคอนดรอปดาวน์จะปรากฏที่ด้านขวาของเซลล์ที่เลือก

• จากนั้น คลิกขวา ที่ชื่อแผ่นงานของแผ่นงานที่ใช้งานอยู่ เลือกตัวเลือก 'View Code'

• คำสั่งข้างต้นจะเปิด VBA module ว่าง ใส่โค้ดต่อไปนี้ใน module นั้น:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Value_Old As String
    Dim Value_New As String
    If Target.Count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    If Not Intersect(Target, ActiveSheet.Range("Country_Range")) Is Nothing Then
        Application.EnableEvents = False
        Value_New = Target.Value
        On Error Resume Next
        Application.Undo
        On Error GoTo 0
        Value_Old = Target.Value
        If InStr(Value_Old, Value_New) Then
            If InStr(Value_Old, ",") Then
                If InStr(Value_Old, ", " & Value_New) Then
                    Target.Value = Replace(Value_Old, ", " & Value_New, "")
                Else
                    Target.Value = Replace(Value_Old, Value_New & ", ", "")
                End If
            Else
                Target.Value = ""
            End If
        Else
            If Value_Old = "" Then
                Target.Value = Value_New
            Else
                If Value_New = "" Then
                    Target.Value = ""
                Else
                    If InStr(Target.Value, Value_New) = 0 Then
                        Target.Value = Value_Old & ", " & Value_New
                    End If
                End If
            End If
        End If
        Application.EnableEvents = True
    Else
        Exit Sub
    End If
End Sub

• คลิกปุ่ม Run หรือกดปุ่ม F5 เพื่อรันโค้ด

• ตอนนี้ เราจะตั้งชื่อให้แมโครและคลิก Run ชื่อของ Macro สำหรับตัวอย่างนี้คือ VBA

• หลังจากนั้น เลือก USA จากเมนูดรอปดาวน์ของเซลล์ B5 มันจะป้อนชื่อประเทศ USA ในเซลล์ B5

• สุดท้าย เลือก Canada และ Mexico จากดรอปดาวน์ด้วย เราเห็นว่าค่าทั้งหมดจากเมนูดรอปดาวน์ถูกเลือกในเซลล์เดียว

อ่านเพิ่มเติม: วิธีสร้าง Excel Drop Down List สำหรับ Data Validation (8 วิธี)

2. แทรก Multiple Selection ไปยังคอลัมน์ติดกัน โดยสร้าง Excel Data Validation Drop-Down List

ในตัวอย่างที่สอง เราจะสร้าง data validation drop-down list สำหรับ multiple selections ในคอลัมน์ที่ติดกัน หากเราเลือกรายการใดจาก data validation drop-down list มันจะถูกเลือกอัตโนมัติในคอลัมน์ที่ติดกัน เราจะดำเนินต่อด้วยชุดข้อมูลก่อนหน้าเพื่อสาธิตตัวอย่างนี้

มา看看ขั้นตอนในการแก้ตัวอย่างนี้

ขั้นตอน:

• ก่อนอื่น เลือกเซลล์ D5 สร้าง data validation drop-down เหมือนวิธีก่อนหน้า ใช้ช่วง (B5:B8) เป็นค่า source สำหรับ validation

• ต่อไป คลิกขวา ที่ชื่อแผ่นงานที่ใช้งานอยู่ และเลือกตัวเลือก 'View Code'

• VBA module ใหม่ว่าง จะเปิดขึ้น เขียนโค้ดต่อไปนี้ใน module ว่างนั้น:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo exitHandler
    Dim DV_Range As Range
    Dim Col_i As Integer
    If Target.Count > 1 Then GoTo exitHandler
    On Error Resume Next
    Set DV_Range = Cells.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo exitHandler
    If DV_Range Is Nothing Then GoTo exitHandler
    If Intersect(Target, DV_Range) Is Nothing Then
    Else
        Application.EnableEvents = False
        If Target.Column = 4 Then
            If Target.Value = "" Then GoTo exitHandler
            If Target.Validation.Value = True Then
                Col_i = Cells(Target.Row, Columns.Count).End(xlToLeft).Column + 1
                Cells(Target.Row, Col_i).Value = Target.Value
            Else
                MsgBox "Invalid entry"
                Target.Activate
            End If
        End If
    End If
exitHandler:
    Application.EnableEvents = True
End Sub

• กดปุ่ม F5 หรือคลิกที่ไอคอน Run เพื่อรันโค้ด

• จากนั้น กล่องโต้ตอบใหม่ชื่อ Macros จะปรากฏ สร้างแมโครชื่อ VBA1 และคลิก Run

• หลังจากนั้น จากไอคอนดรอปดาวน์ของเซลล์ D5 เลือกประเทศชื่อ USA

• คำสั่งข้างต้นป้อนชื่อประเทศ USA ในเซลล์ E5 ซึ่งอยู่ในคอลัมน์ E ที่ติดกัน

• สุดท้าย เลือก Canada และ Mexico เราเห็นว่าค่าที่เลือกไปไว้ในคอลัมน์ที่ติดกันตามลำดับ

อ่านเพิ่มเติม: Default Value in Data Validation List with Excel VBA (Macro and UserForm)

บทความคล้ายกัน:

• Autocomplete Data Validation Drop Down List in Excel (2 Methods)
• วิธีสร้าง Data Validation List จากตารางใน Excel (3 วิธี)
• Apply Multiple Data Validation in One Cell in Excel (3 Examples)
• วิธيلบช่องว่างจาก Data Validation List ใน Excel (5 วิธี)
• Excel Data Validation Alphanumeric Only (Using Custom Formula)

3. ทำ Multiple Selection ในแถวแยกกัน ด้วย Data Validation Drop-Down List ใน Excel

ตัวอย่างที่สาม คล้ายตัวอย่างที่สองมาก ในตัวอย่างที่สอง multiple selections เกิดขึ้นในคอลัมน์ที่ติดกัน ในขณะที่ในตัวอย่างนี้ multiple selections จะเกิดขึ้นในแถวแยกกัน ตัวอย่างเช่น เพื่อสร้าง data validation drop-down list สำหรับ multiple selections ในตัวอย่างนี้ เราจะดำเนินต่อด้วยชุดข้อมูลที่เราใช้ในตัวอย่างก่อนหน้า

มาผ่านขั้นตอนในการดำเนินการนี้

ขั้นตอน:

• ในตอนแรก เลือกเซลล์ D5 เหมือนกับขั้นตอนก่อนหน้า สร้าง data validation drop-down ใช้ช่วง (B5:B8) เป็นค่า source สำหรับ validation

• ต่อไป คลิกขวา ที่แผ่นงานที่ใช้งานอยู่ จากตัวเลือกที่มี ให้เลือก 'View Code'

• การกระทำข้างต้นเปิด VBA module ว่าง ใส่โค้ดต่อไปนี้ใน module นั้น:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo exitHandler
    Dim DV_Range As Range
    Dim Row_1 As Long
    Dim Col_1 As Long
    Col_1 = Target.Column
    If Target.Count > 1 Then GoTo exitHandler
    On Error Resume Next
    Set DV_Range = Cells.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo exitHandler
    If DV_Range Is Nothing Then GoTo exitHandler
    If Intersect(Target, DV_Range) Is Nothing Then
    Else
        If Target.Value = "" Then GoTo exitHandler
        Application.EnableEvents = False
        Select Case Target.Column
            Case 4
                If Target.Offset(0, 1).Value = "" Then
                    Row_1 = Target.Row
                Else
                    Row_1 = Cells(Rows.Count, Col_1 + 1).End(xlUp).Row + 1
                End If
                Cells(Row_1, Col_1 + 1).Value = Target.Value
                Target.ClearContents
        End Select
    End If
exitHandler:
    Application.EnableEvents = True
End Sub

• เพื่อรันโค้ด คลิกที่ไอคอน Run หรือกดปุ่ม F5

• จากนั้น เราเห็นกล่องโต้ตอบใหม่ชื่อ Macros ตั้งชื่อแมโครเป็น VBA2 และคลิก Run

• หลังจากนั้น จากรายการดรอปดาวน์ของเซลล์ D5 เลือกตัวเลือก USA

• ดังนั้น เราเห็นว่าชื่อ USA ปรากฏในแถวเดียวกันแต่ในคอลัมน์ที่ติดกัน

• ในที่สุด เลือก Canada, Mexico, และ England ทีละรายการ เราจะเห็นว่าค่าที่เลือกไปไว้ในคอลัมน์เดียวกันแต่ในแถวแยกกันทีละแถว

อ่านเพิ่มเติม: Excel Data Validation Drop Down List with Filter (2 Examples)

สรุป

ดังนั้น บทช่วยสอนนี้ให้ภาพรวมของการสร้าง data validation drop-down list สำหรับ multiple selections เพื่อทดสอบทักษะของคุณ ดาวน์โหลดสมุดงานฝึกปฏิบัติที่มาพร้อมกับบทความนี้ โปรดทิ้งความคิดเห็นในกล่องด้านล่าง หากคุณมีคำถาม ทีมของเราจะพยายามตอบกลับโดยเร็วที่สุด ในอนาคต ให้ติดตามโซลูชัน Microsoft Excel ที่นวัตกรรมมากขึ้น

บทความเกี่ยวข้อง

• วิธีใช้ IF Statement ใน Data Validation Formula ใน Excel (6 วิธี)
• Use Data Validation in Excel with Color (4 Ways)
• วิธีใช้ Data Validation List จากแผ่นงานอื่น (6 วิธี)
• Use Named Range for Data Validation List with VBA in Excel
• [Fixed] Data Validation Not Working for Copy Paste in Excel (with Solution)