ดรอปดาวน์ รายการเป็นคุณลักษณะที่มีประโยชน์มากในการทำงานต่างๆ ที่เกี่ยวข้องกับ Excel การใช้งาน VBA เป็นวิธีที่มีประสิทธิภาพ รวดเร็วที่สุด และปลอดภัยที่สุดในการรันการดำเนินการใดๆ ใน Excel ในบทความนี้ เราจะแสดงให้คุณเห็น 2 วิธีที่มีประสิทธิภาพในการเลือกค่าจากรายการแบบเลื่อนลง ใน Excel ด้วย มาโคร VBA .
ดาวน์โหลดสมุดงาน
คุณสามารถดาวน์โหลดเวิร์กบุ๊ก Excel แบบฝึกหัดฟรีได้จากที่นี่
การสร้างรายการดรอปดาวน์จากรายการทั่วไปใน Excel
ก่อนดำดิ่งสู่ส่วนการเขียนโค้ด เรามาเรียนรู้วิธีง่ายๆ ในการสร้างรายการดรอปดาวน์ จากรายการทั่วไป ใน Excel เพื่อให้สามารถใช้ รายการแบบเลื่อนลงที่เราสร้างขึ้นได้ในภายหลัง เป็นตัวอย่างบทความนี้
ต่อไปนี้เป็นรายการทั่วไปที่เรามีในแผ่นงาน Excel ของเรา มีค่าที่ซ้ำกัน (เช่น Apple ใน เซลล์ B7 และ B9 ) ในรายการ
เราจะดูวิธีการสร้าง รายการแบบเลื่อนลงประกอบด้วยค่า (เช่น องุ่น ส้ม แอปเปิ้ล มะม่วง แอปเปิ้ล ) จากรายการทั่วไป .
ขั้นตอน:
- ในตอนแรก คลิกที่เซลล์ใดก็ได้ (เซลล์ D4 ในกรณีของเรา) ที่คุณต้องการจัดเก็บรายการแบบเลื่อนลง
- จากนั้น คลิกที่แท็บ ข้อมูล .
- หลังจากนั้น เลือก การตรวจสอบข้อมูล จาก เครื่องมือข้อมูล กลุ่มริบบิ้น
- A การตรวจสอบข้อมูล กล่องป๊อปอัปจะปรากฏขึ้น จากนั้น
- เลือก รายการ ใน อนุญาต เกณฑ์
- ใน ที่มา เกณฑ์ ลากช่วง (B5:B9 ในกรณีของเรา) ที่มีค่าสำหรับรายการแบบเลื่อนลง
- ภายหลัง คลิก ตกลง .
ดูภาพต่อไปนี้
ใน เซลล์ D4 มี รายการแบบเลื่อนลงที่สร้างขึ้น ซึ่งถือ ค่า (เช่น องุ่น ส้ม แอปเปิ้ล มะม่วง แอปเปิ้ล ) ดึงข้อมูลจากรายการทั่วไป (ช่วง B5:B9 )
2 วิธีกับ VBA เพื่อเลือกค่าจากรายการแบบหล่นลงใน Excel
ในส่วนนี้ คุณจะได้เรียนรู้วิธีเลือกค่าหลายค่าที่มีทั้งค่าซ้ำและไม่ซ้ำจากรายการแบบเลื่อนลง ใน Excel ด้วย VBA .
1. ฝัง VBA เพื่อเลือกหลายค่าจากรายการดรอปดาวน์ใน Excel (ด้วยค่าที่ซ้ำกัน)
เรามีค่าซ้ำกันในชุดข้อมูลของเรา หากคุณต้องการให้รายการดรอปดาวน์ของคุณจับค่าทั้งหมดไม่ว่าค่าจะเป็นสองเท่าหรือไม่ จากนั้นทำตามขั้นตอนด้านล่าง
ขั้นตอน:
- ในตอนแรก ให้กด Alt + F11 บนแป้นพิมพ์ของคุณหรือไปที่แท็บ นักพัฒนา -> Visual Basic เพื่อเปิด Visual Basic Editor .
- ถัดไป คลิกขวาที่ชื่อแผ่นงานที่ต้องการ และเลือก ดูโค้ด จากรายการตัวเลือกที่ปรากฏ
- จากนั้น คัดลอก รหัสต่อไปนี้และ วาง ลงในหน้าต่างโค้ด
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
- อย่าวิ่ง รหัสนี้ บันทึก มัน.
- ตอนนี้ กลับไปที่แผ่นงาน ที่น่าสนใจ หากคุณ คลิกที่รายการแบบเลื่อนลงที่สร้างขึ้น ใน เซลล์ D4 คุณจะเห็นว่าขณะนี้คุณสามารถ เลือกค่าได้หลายค่าจากเมนูแบบเลื่อนลง (ดูที่ gif ต่อไปนี้)
ดังที่คุณเห็นจาก gif ด้านบน คุณยังสามารถเลือกค่าหนึ่งๆ ได้หลายครั้ง ด้วย VBA . นี้ รหัส. รหัสมาโครที่เราให้ไว้ในส่วนนี้จะให้รายการแบบเลื่อนลงเลือกค่าทุกประเภท .
คำอธิบายโค้ด VBA
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ตั้งค่าปลายทางเป็นเซลล์ D4 ซึ่งมีการตรวจสอบข้อมูล หากไม่มีเซลล์ที่มีการตรวจสอบความถูกต้องของข้อมูล ให้ไปที่ป้ายกำกับ ออก .
Else: If Target.Value = "" Then GoTo Exitsub Elseหากไม่มีเซลล์ปลายทาง ให้ไปที่ป้ายกำกับ ออก . มิฉะนั้น ให้ดำเนินการตามบรรทัดต่อไปนี้
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มิฉะนั้น ให้ตั้งค่าทั้งค่าเก่าและค่าใหม่เป็นค่าปลายทางโดยเชื่อมด้วยเครื่องหมายจุลภาค (, ). ปิดทั้งหมด ถ้า แถลงการณ์
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = Trueย้อนกลับไปที่ Application Events
อ่านเพิ่มเติม: วิธีสร้างรายการดรอปดาวน์ใน Excel ด้วยการเลือกหลายรายการ
การอ่านที่คล้ายกัน:
- วิธีสร้างรายการแบบหล่นลงจากชีตอื่นใน Excel (2 วิธี)
- สร้างรายการดรอปดาวน์ที่ค้นหาได้ใน Excel (2 วิธี)
- รายการแบบหล่นลงของ Excel ไม่ทำงาน (8 ปัญหาและวิธีแก้ไข)
- สร้างรายการแบบหล่นลงของ Excel จากตาราง (5 ตัวอย่าง)
- อัปเดตรายการแบบเลื่อนลงอัตโนมัติใน Excel (3 วิธี)
2. ใช้มาโคร VBA เพื่อเลือกหลายค่าจากรายการแบบหล่นลง (โดยไม่มีค่าซ้ำ)
เรามีค่าซ้ำกันในชุดข้อมูลของเรา หากคุณต้องการให้รายการแบบเลื่อนลงจับ ค่าทั้งหมด ยกเว้นค่าที่ซ้ำกัน จากนั้นทำตามขั้นตอนด้านล่าง
ขั้นตอน:
- ดังที่แสดงก่อนหน้านี้ เปิด Visual Basic Editor จาก นักพัฒนา แท็บ
- จากนั้นไปที่ หน้าต่างโค้ด จาก ดูโค้ด ตัวเลือกปรากฏขึ้นโดยคลิกขวา แผ่นงาน ที่น่าสนใจ
- จากนั้น คัดลอก รหัสต่อไปนี้และ วาง ลงในหน้าต่างโค้ดของเวิร์กชีตที่ระบุ
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
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
- อย่าวิ่ง รหัสนี้ บันทึก มัน.
- ตอนนี้ กลับไปที่แผ่นงาน ที่น่าสนใจ หากคุณ คลิกที่รายการแบบเลื่อนลงที่สร้างขึ้น ใน เซลล์ D4 คุณจะเห็นว่าขณะนี้คุณสามารถ เลือกค่าได้หลายค่าจากเมนูแบบเลื่อนลง (ดูที่ gif ต่อไปนี้)
ดังที่คุณเห็นจาก gif ด้านบน คุณ ไม่สามารถเลือกค่าใดค่าหนึ่งได้หลายครั้ง ด้วย VBA . นี้ รหัส. รหัสมาโครที่เราให้ไว้ในส่วนนี้จะให้รายการแบบเลื่อนลงเลือกค่าโดยไม่มีค่าซ้ำกัน .
คำอธิบายโค้ด VBA
Dim ValueA As String
Dim ValueB As Stringการกำหนดชื่อตัวแปร
Application.EnableEvents = Trueย้อนกลับไปที่ Application Events
On Error GoTo Exitsubหากเกิดข้อผิดพลาด ให้ไปที่ป้ายกำกับ ออก .
If Target.Address = "$D$4" Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsubตั้งค่าปลายทางเป็นเซลล์ D4 ซึ่งมีการตรวจสอบข้อมูล หากไม่มีเซลล์ที่มีการตรวจสอบความถูกต้องของข้อมูล ให้ไปที่ป้ายกำกับ ออก .
Else: If Target.Value = "" Then GoTo Exitsub Elseหากไม่มีเซลล์ปลายทาง ให้ไปที่ป้ายกำกับ ออก . มิฉะนั้น ให้ดำเนินการตามบรรทัดต่อไปนี้
Application.EnableEvents = Falseการปิด Application Events เพื่อให้ Worksheet_Change สามารถป้องกันไม่ให้มาโครเริ่มทำงานซึ่งอาจทำให้เกิดการวนซ้ำที่ไม่สิ้นสุดได้
ValueB = Target.Valueการกำหนด ValueB ให้เป็นค่าใหม่ของเซลล์ที่เปลี่ยนแปลง
Application.Undoหากต้องการเลิกทำเซลล์ที่เปลี่ยนแปลง
ValueA = Target.Valueด้วยการเลิกทำการเปลี่ยนแปลง ตอนนี้เราสามารถกำหนด ValueA ให้เป็นค่าเดิมของเซลล์ที่เปลี่ยนแปลง
If ValueA = "" Then
Target.Value = ValueBหากค่าเดิมว่างเปล่า ให้เก็บค่าใหม่เป็นปลายทาง
Else
If InStr(1, ValueA, ValueB) = 0 Then
Target.Value = ValueA & ", " & ValueBฟังก์ชัน InStr ส่งกลับตำแหน่งของสตริงย่อยที่เกิดขึ้นครั้งแรกในสตริง หากผลลัพธ์เป็น 0 จากนั้นตั้งค่าทั้งค่าเก่าและค่าใหม่เป็นค่าปลายทางโดยเชื่อมด้วยเครื่องหมายจุลภาค (, )
Else: Target.Value = ValueA
End If
End If
End If
End Ifมิฉะนั้น ให้ตั้งค่าเดิมเป็นปลายทาง ปิดทั้งหมด ถ้า แถลงการณ์
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = Trueย้อนกลับไปที่ Application Events
อ่านเพิ่มเติม: รายการแบบหล่นลงของ Excel ขึ้นอยู่กับการเลือก
บทสรุป
สรุป บทความนี้แสดงให้คุณเห็น 2 วิธีที่มีประสิทธิภาพในการเลือกค่าจากรายการแบบเลื่อนลง ใน Excel ด้วย มาโคร VBA . ฉันหวังว่าบทความนี้จะเป็นประโยชน์กับคุณมาก อย่าลังเลที่จะถามคำถามใด ๆ เกี่ยวกับหัวข้อนี้
บทความที่เกี่ยวข้อง
- วิธีการสร้างรายการแบบหล่นลงตามสูตรใน Excel (4 วิธี)
- รายการแบบเลื่อนลงตามเงื่อนไขใน Excel (สร้าง จัดเรียง และใช้งาน)
- วิธีใช้คำสั่ง IF เพื่อสร้างรายการดรอปดาวน์ใน Excel
- เชื่อมโยงค่าของเซลล์กับรายการดรอปดาวน์ใน Excel (5 วิธี)
- วิธีลบรายการดรอปดาวน์ใน Excel
