วิธีเลือกค่าจาก Drop-Down List ใน Excel ด้วย VBA (2 วิธีที่พิสูจน์แล้ว)

รายการดรอปดาวน์ (Dropdown) เป็นคุณสมบัติที่มีประโยชน์มากสำหรับการทำงานต่างๆ ใน Excel การใช้ VBA เป็นวิธีที่มีประสิทธิภาพ เร็วที่สุด และปลอดภัยที่สุดในการรันการดำเนินการใดๆ ใน Excel ในบทความนี้ เราจะแสดง 2 วิธีที่มีประสิทธิภาพสำหรับการ เลือกค่าจาก drop down list ใน Excel ด้วย VBA macro

ดาวน์โหลดสมุดงาน

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

การสร้าง Dropdown List จากรายการทั่วไปใน Excel

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

ต่อไปนี้คือรายการทั่วไปที่เรามีในแผ่นงาน Excel มี ค่าที่ซ้ำกัน (เช่น Apple ใน Cell B7 และ B9) ในรายการ

วิธีเลือกค่าจาก Drop-Down List ใน Excel ด้วย VBA (2 วิธีที่พิสูจน์แล้ว)

เราจะดูวิธีการสร้าง dropdown list ที่ประกอบด้วยค่า (เช่น Grapes, Orange, Apple, Mango, Apple) จากรายการทั่วไป

ขั้นตอน:

ก่อนอื่น คลิกที่เซลล์ใดๆ (Cell D4 ในกรณีของเรา) ที่คุณต้องการจัดเก็บ dropdown list
จากนั้น คลิกที่แท็บ Data
หลังจากนั้น เลือก Data Validation จากกลุ่ม Data Tools ของริบบอน

วิธีเลือกค่าจาก Drop-Down List ใน Excel ด้วย VBA (2 วิธีที่พิสูจน์แล้ว)

กล่องป๊อปอัป Data Validation จะปรากฏ จากนั้น
- เลือก List ในเกณฑ์ Allow
- ในเกณฑ์ Source ลากช่วง (B5:B9 ในกรณีของเรา) ที่มีค่าสำหรับ dropdown list

วิธีเลือกค่าจาก Drop-Down List ใน Excel ด้วย VBA (2 วิธีที่พิสูจน์แล้ว)

ต่อไป คลิก OK

ดูภาพต่อไปนี้

วิธีเลือกค่าจาก Drop-Down List ใน Excel ด้วย VBA (2 วิธีที่พิสูจน์แล้ว)

ใน Cell D4 มี dropdown list ที่สร้างขึ้น ซึ่งเก็บ ค่า (เช่น Grapes, Orange, Apple, Mango, Apple) ที่ดึงมา จากรายการทั่วไป (ช่วง B5:B9)

2 วิธีด้วย VBA เพื่อเลือกค่าจาก Drop Down List ใน Excel

ในส่วนนี้ คุณจะได้เรียนรู้วิธี เลือกหลายค่าทั้งที่ซ้ำและไม่ซ้ำจาก dropdown list ใน Excel ด้วย VBA

1. ใช้ VBA เลือกหลายค่าจาก Drop Down List ใน Excel (มีค่าซ้ำ)

เรามีค่าที่ซ้ำกันในชุดข้อมูล หากคุณต้องการให้ dropdown list จับค่าทั้งหมดไม่ว่าค่าจะซ้ำหรือไม่ ให้ทำตามขั้นตอนด้านล่าง

ขั้นตอน:

ในตอนแรก กด Alt + F11 บนคีย์บอร์ด หรือไปที่แท็บ Developer -> Visual Basic เพื่อเปิด Visual Basic Editor

วิธีเลือกค่าจาก Drop-Down List ใน Excel ด้วย VBA (2 วิธีที่พิสูจน์แล้ว)

ต่อไป คลิกขวาที่ชื่อแผ่นงานที่เหมาะสม และเลือก View Code จากรายการตัวเลือกที่ปรากฏ

วิธีเลือกค่าจาก Drop-Down List ใน Excel ด้วย VBA (2 วิธีที่พิสูจน์แล้ว)

จากนั้น คัดลอก โค้ดต่อไปนี้และ วาง ลงในหน้าต่างโค้ด

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ValueA As String
    Dim ValueB As String
    On Error GoTo Exitsub
    If Target.Address = "$D$4" Then
        If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then GoTo Exitsub
        Else: If Target.Value = "" Then GoTo Exitsub
        Else
            Application.EnableEvents = False
            ValueB = Target.Value
            Application.Undo
            ValueA = Target.Value
            If ValueA = "" Then Target.Value = ValueB
            Else Target.Value = ValueA & ", " & ValueB
            End If
        End If
    End If
    Application.EnableEvents = True
Exitsub:
    Application.EnableEvents = True
End Sub

วิธีเลือกค่าจาก Drop-Down List ใน Excel ด้วย VBA (2 วิธีที่พิสูจน์แล้ว)

อย่ารัน โค้ดนี้ บันทึก มัน
ตอนนี้ กลับไปที่แผ่นงาน ที่สนใจ หากคุณ คลิกที่ dropdown list ที่สร้างขึ้น ใน Cell D4 คุณจะเห็นว่าตอนนี้คุณสามารถ เลือกหลายค่าจาก dropdown (ดู gif ด้านล่าง)

ตามที่คุณเห็นจาก gif ด้านบน คุณแม้แต่สามารถ เลือกค่าเฉพาะเจาะจงหลายครั้ง ด้วย VBA โค้ดนี้ โค้ดแมโครที่เราให้มาในส่วนนี้จะทำให้ dropdown list เลือกค่าทุกประเภทได้

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

Dim ValueA As String
Dim ValueB As String

กำหนดชื่อตัวแปร

On Error GoTo Exitsub

หากเกิดข้อผิดพลาด ให้ไปที่ฉลับ Exitsub

If Target.Address = "$D$4" Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then GoTo Exitsub

ตั้งค่าจุดหมายเป็นเซลล์ D4 ซึ่งมี Data Validation หากไม่มีเซลล์ที่มี Data Validation ให้ไปที่ฉลับ Exitsub

Else: If Target.Value = "" Then GoTo Exitsub Else

หากไม่มีเซลล์เป็นจุดหมาย ให้ไปที่ฉลับ Exitsub มิฉะนั้น ให้ดำเนินการบรรทัดต่อไป

Application.EnableEvents = False

ปิด Application Events เพื่อป้องกันไม่ให้แมโคร Worksheet_Change ทำงานซ้ำซ้อน ซึ่งอาจทำให้เกิดลูปไม่รู้จบ

ValueB = Target.Value

กำหนด ValueB ให้เป็นค่าใหม่ของเซลล์ที่เปลี่ยนแปลง

Application.Undo

เพื่อยกเลิกการเปลี่ยนแปลงของเซลล์

ValueA = Target.Value

โดยการยกเลิกการเปลี่ยนแปลง เราสามารถกำหนด ValueA ให้เป็นค่าเก่าของเซลล์ที่เปลี่ยนแปลง

If ValueA = "" Then Target.Value = ValueB

หากค่าเก่าเป็นว่าง ให้เก็บค่าใหม่เป็นจุดหมาย

Else Target.Value = ValueA & ", " & ValueB
End If
End If
End If

มิฉะนั้น ให้ตั้งค่าทั้งค่าเก่าและค่าใหม่เป็นค่าจุดหมายโดยต่อกันด้วยคอมม่า (,) ปิดคำสั่ง If ทั้งหมด

Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True

เปิด Application Events กลับคืน

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

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

วิธีสร้าง Drop Down List จากแผ่นงานอื่นใน Excel (2 วิธี)
สร้าง Searchable Drop Down List ใน Excel (2 วิธี)
Excel Drop Down List Not Working (8 ปัญหาและวิธีแก้)
สร้าง Excel Drop Down List จากตาราง (5 ตัวอย่าง)
Auto Update Drop Down List ใน Excel (3 วิธี)

2. ใช้ VBA Macro เลือกหลายค่าจาก Drop Down List (ไม่มีค่าซ้ำ)

เรามีค่าที่ซ้ำกันในชุดข้อมูล หากคุณต้องการให้ dropdown list จับ ค่าทั้งหมดยกเว้นค่าที่ซ้ำ ให้ทำตามขั้นตอนด้านล่าง

ขั้นตอน:

ตามที่แสดงไว้ก่อนหน้า เปิด Visual Basic Editor จากแท็บ Developer
จากนั้น ไปที่ หน้าต่างโค้ด จากตัวเลือก View Code ที่ปรากฏจากการ คลิกขวา ที่ แผ่นงาน ที่สนใจ
จากนั้น คัดลอก โค้ดต่อไปนี้และ วาง ลงในหน้าต่างโค้ดของแผ่นงานที่ระบุ

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ValueA As String
    Dim ValueB As String
    Application.EnableEvents = True
    On Error GoTo Exitsub
    If Target.Address = "$D$4" Then
        If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then GoTo Exitsub
        Else: If Target.Value = "" Then GoTo Exitsub
        Else
            Application.EnableEvents = False
            ValueB = Target.Value
            Application.Undo
            ValueA = Target.Value
            If ValueA = "" Then Target.Value = ValueB
            Else If InStr(1, ValueA, ValueB) = 0 Then Target.Value = ValueA & ", " & ValueB
            Else: Target.Value = ValueA
            End If
        End If
    End If
    Application.EnableEvents = True
Exitsub:
    Application.EnableEvents = True
End Sub

วิธีเลือกค่าจาก Drop-Down List ใน Excel ด้วย VBA (2 วิธีที่พิสูจน์แล้ว)

อย่ารัน โค้ดนี้ บันทึก มัน
ตอนนี้ กลับไปที่แผ่นงาน ที่สนใจ หากคุณ คลิกที่ dropdown list ที่สร้างขึ้น ใน Cell D4 คุณจะเห็นว่าตอนนี้คุณสามารถ เลือกหลายค่าจาก dropdown (ดู gif ด้านล่าง)

วิธีเลือกค่าจาก Drop-Down List ใน Excel ด้วย VBA (2 วิธีที่พิสูจน์แล้ว)

ตามที่คุณเห็นจาก gif ด้านบน คุณ ไม่สามารถเลือกค่าเฉพาะเจาะจงหลายครั้ง ด้วย VBA โค้ดนี้ โค้ดแมโครที่เราให้มาในส่วนนี้จะทำให้ dropdown list เลือกค่าโดยไม่มีค่าซ้ำ

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

Dim ValueA As String
Dim ValueB As String

กำหนดชื่อตัวแปร

Application.EnableEvents = True

เปิด Application Events กลับคืน

On Error GoTo Exitsub

หากเกิดข้อผิดพลาด ให้ไปที่ฉลับ Exitsub

If Target.Address = "$D$4" Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then GoTo Exitsub

Else: If Target.Value = "" Then GoTo Exitsub Else

Application.EnableEvents = False

ปิด Application Events เพื่อป้องกันไม่ให้แมโคร Worksheet_Change ทำงานซ้ำซ้อนที่อาจทำให้เกิดลูปไม่รู้จบ

ValueB = Target.Value

กำหนด ValueB ให้เป็นค่าใหม่ของเซลล์ที่เปลี่ยนแปลง

Application.Undo

เพื่อยกเลิกการเปลี่ยนแปลงของเซลล์

ValueA = Target.Value

If ValueA = "" Then Target.Value = ValueB

หากค่าเก่าเป็นว่าง ให้เก็บค่าใหม่เป็นจุดหมาย

Else If InStr(1, ValueA, ValueB) = 0 Then Target.Value = ValueA & ", " & ValueB

ฟังก์ชัน InStr คืนตำแหน่งของการปรากฏครั้งแรกของ substring ใน string หากผลลัพธ์เป็น 0 ให้ตั้งค่าทั้งค่าเก่าและค่าใหม่เป็นค่าจุดหมายโดยต่อกันด้วยคอมม่า (,)

Else: Target.Value = ValueA
End If
End If
End If
End If

มิฉะนั้น ให้ตั้งค่าค่าเก่าเป็นจุดหมาย ปิดคำสั่ง If ทั้งหมด

Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True

เปิด Application Events กลับคืน

อ่านเพิ่มเติม: Excel Drop Down List Depending on Selection

สรุป

สรุปแล้ว บทความนี้แสดง 2 วิธีที่มีประสิทธิภาพเกี่ยวกับวิธี เลือกค่าจาก drop down list ใน Excel ด้วย VBA macro หวังว่าบทความนี้จะเป็นประโยชน์มากต่อคุณ รู้สึกสบายใจถามคำถามใดๆ เกี่ยวกับหัวข้อนี้

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

วิธีสร้าง Drop-Down List ตามสูตรใน Excel (4 วิธี)
Conditional Drop Down List ใน Excel (สร้าง เรียงลำดับ และใช้)
วิธีใช้ IF Statement สร้าง Drop-Down List ใน Excel
เชื่อมโยงค่าเซลล์กับ Drop Down List ใน Excel (5 วิธี)
วิธิลบ Drop-Down List ใน Excel