7 รายการแบบเลื่อนลง Data Validation แบบ VBA สำหรับการเชี่ยวชาญ Excel

รายการแบบเลื่อนลงของ Data Validation เป็นคุณสมบัติที่มีประโยชน์มากสำหรับการทำงานต่างๆ ใน Excel การใช้ VBA เป็นวิธีที่มีประสิทธิภาพ เร็วที่สุด และปลอดภัยที่สุดในการรันการดำเนินการใดๆ ใน Excel ในบทความนี้ เราจะแสดงให้คุณเห็น 7 การประยุกต์ใช้ที่แตกต่างกันของ รายการแบบเลื่อนลง Data Validation ใน Excel ด้วย VBA Macro

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

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

7 วิธีการใช้ VBA กับรายการแบบเลื่อนลง Data Validation ใน Excel

ในส่วนนี้ คุณจะได้เรียนรู้ 7 การประยุกต์ใช้ที่แตกต่างกันของ รายการแบบเลื่อนลง Data Validation ใน Excel ด้วย VBA

1. ใช้ VBA สร้างรายการแบบเลื่อนลง Data Validation ใน Excel

เพื่อทราบวิธี สร้างรายการแบบเลื่อนลง Data Validation ด้วย VBA ให้ทำตามขั้นตอนด้านล่าง

ขั้นตอน:

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

• จากนั้น ในหน้าต่างโค้ดที่โผล่มา จากแถบเมนู คลิก Insert -> Module

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

Sub CreateDropDownList() Range("B5").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="Grapes, Orange, Guava, Mango, Apple" End Sub

โค้ดของคุณพร้อมรันแล้ว

ชิ้นโค้ดนี้จะสร้างรายการแบบเลื่อนลงใน เซลล์ B5 และรายการจะมีค่า Grapes, Orange, Guava, Mango, Apple

• ตอนนี้ กด F5 บนคีย์บอร์ด หรือจากแถบเมนูเลือก Run -> Run Sub/UserForm คุณสามารถคลิกที่ ไอคอน Run ขนาดเล็ก ในแถบเมนูย่อยเพื่อรันแมโครได้เช่นกัน

หลังจากรันโค้ดเสร็จ ให้ดูภาพด้านล่างเพื่อดูผลลัพธ์

จากภาพด้านบน จะเห็นว่ามีรายการแบบเลื่อนลงสร้างขึ้นใน เซลล์ B5 พร้อมค่า Grapes, Orange, Guava, Mango, Apple

อ่านเพิ่มเติม: วิธีสร้างรายการแบบเลื่อนลงของ Excel สำหรับ Data Validation (8 วิธี)

2. สร้างรายการแบบเลื่อนลงด้วย Named Range ด้วย VBA ใน Excel

เมื่อคุณไม่ต้องการเขียนค่าทุกค่าของรายการแบบเลื่อนลงลงในโค้ด คุณสามารถใส่ชื่อทั้งหมดไว้ใน Defined Name แล้วนำมาใช้เรียกช่วงค่าได้ นี่เป็นวิธีที่สะดวกมากสำหรับการสร้างรายการแบบเลื่อนลงใน Excel

ในส่วนนี้ คุณจะได้เรียนรู้วิธี สร้างรายการแบบเลื่อนลงจากรายการที่กำหนดโดยใช้ Named Range ด้วยโค้ด VBA

ขั้นตอน:

• ขั้นแรก เลือกช่วง ที่มีค่าของรายการแบบเลื่อนลง (ในกรณีนี้ ช่วงคือ B5:B9)
• ขั้นที่สอง คลิกขวา บนช่วงที่เลือก
• จะมีรายการตัวเลือกปรากฏ จากนั้นเลือก Define Name…

• หลังจากนั้น กล่องโต้ตอบ New Name จะปรากฏ ในกล่อง Name ให้พิมพ์ ชื่อใดๆ ที่คุณชอบ (เราตั้งชื่อเซลล์ว่า Fruits)
• ต่อไป คลิก OK

• ตอนนี้เราได้ตั้งชื่อช่วง B5:B9 ว่า Fruits เรียบร้อยแล้ว (แสดงในภาพด้านล่าง)

ตอนนี้เราจะใช้ Defined Name นี้ในโค้ด VBA ขั้นตอนการทำแสดงด้านล่าง

• เช่นเดิม เปิด Visual Basic Editor จากแท็บ Developer และ Insert Module ในหน้าต่างโค้ด
• จากนั้น ในหน้าต่างโค้ด คัดลอก โค้ดต่อไปนี้ และ วาง ลงไป

Sub GenerateDropDownList() Range("B12").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=Fruits" End Sub

โค้ดพร้อมรันแล้ว

ชิ้นโค้ดนี้จะสร้างรายการแบบเลื่อนลงใน เซลล์ B12 ด้วยค่า Grapes, Orange, Guava, Mango, Apple ที่ถูกกำหนดในชื่อ Fruits

• ต่อไป Run แมโคร ผลลัพธ์แสดงในภาพด้านล่าง

ผลลัพธ์คือ มี รายการแบบเลื่อนลงสร้างในเซลล์ B12 ด้วยค่า Grapes, Orange, Guava, Mango, Apple

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

3. สร้างกล่องแบบเลื่อนลง Data Validation จากรายการที่กำหนดด้วยแมโคร

ถ้าคุณไม่ชอบตัวเลือก Named Range ส่วนนี้เหมาะกับคุณ ที่นี่ คุณจะได้เรียนรู้วิธี สร้างรายการแบบเลื่อนลงจากช่วง ที่มีอยู่ในชุดข้อมูล

ขั้นตอน:

• ตามที่แสดงไว้ก่อนหน้า เปิด Visual Basic Editor จากแท็บ Developer และ Insert Module ในหน้าต่างโค้ด
• จากนั้น คัดลอก โค้ดต่อไปนี้ และ วาง ลงในหน้าต่างโค้ด

Sub ProduceDropDownList() With Range("B12").Validation .Add xlValidateList, xlValidAlertStop, xlBetween, "=$B$5:$B$10" .InCellDropdown = True End With End Sub

โค้ดพร้อมรันแล้ว

ชิ้นโค้ดนี้จะสร้าง รายการแบบเลื่อนลงในเซลล์ B12 ด้วย ค่าที่อยู่ในช่วง B5:B9

• ตอนนี้ Run แมโคร และดูภาพด้านล่างเพื่อดูผลลัพธ์

ผลลัพธ์คือ มี รายการแบบเลื่อนลงสร้างในเซลล์ B12 ด้วยค่า Grapes, Orange, Guava, Mango, Apple ที่เราเก็บไว้ใน เซลล์ B5 ถึง B9 ในเวิร์กชีต

เนื้อหาที่เกี่ยวข้อง: Default Value in Data Validation List with Excel VBA (Macro and UserForm)

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

• Excel Data Validation Drop Down List with Filter (2 Methods)
• Data Validation Alphanumeric Only (Using Custom Formula)
• Excel Data Validation Based on Another Cell Value
• วิธีใช้สูตร VLOOKUP แบบกำหนดเองใน Data Validation ของ Excel

4. ใช้ VBA สร้างรายการแบบเลื่อนลงหลายรายการใน Excel

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

ขั้นตอน:

• ก่อนอื่น เปิด Visual Basic Editor จากแท็บ Developer และ Insert Module ในหน้าต่างโค้ด
• ขั้นที่สอง ในหน้าต่างโค้ด คัดลอก โค้ดต่อไปนี้ และ วาง ลงไป

Sub MultipleDropDownList(iTarget As Range, iSource As Range) 'to delete and add validation in the target range With iTarget.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=" & iSource.Address .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End Sub Sub DropDownRange() MultipleDropDownList Sheet7.Range("B5:B10"), Sheet7.Range("A1:A3") End Sub

โค้ดพร้อมรันแล้ว

โค้ดนี้จะ สร้างรายการแบบเลื่อนลงในทุกเซลล์จากช่วง B5 ถึง B9

• ขั้นที่สาม Run แมโคร ดู GIF ด้านล่างเพื่อดูผลลัพธ์

ทุกเซลล์จากช่วง B5 ถึง B9 ตอนนี้มี รายการแบบเลื่อนลง อยู่แล้ว

อ่านเพิ่มเติม: ใช้ Data Validation แบบกำหนดเองสำหรับเกณฑ์หลายข้อใน Excel (4 ตัวอย่าง)

5. ใช้ VBA สร้างรายการแบบเลื่อนลงพร้อมฟังก์ชันที่ผู้ใช้กำหนด (UDF)

คุณยังสามารถ สร้างรายการแบบเลื่อนลงด้วยฟังก์ชันที่ผู้ใช้กำหนด (UDF) ใน Excel ได้ ขั้นตอนการทำแสดงด้านล่าง

ขั้นตอน:

• ก่อนอื่น คลิกขวาที่ชีต ที่คุณต้องการใช้ UDF เพื่อสร้างรายการแบบเลื่อนลง
• จากนั้น เลือก View Code จากรายการที่ปรากฏ ตามภาพด้านล่าง เรา คลิกขวา ที่ ชีตชื่อ UDF ที่เก็บชุดข้อมูลของเรา และเลือก View Code จากตัวเลือก

• จากนั้น คัดลอก โค้ดต่อไปนี้ และ วาง ลงในหน้าต่างโค้ดที่สร้างขึ้นอัตโนมัติ

Public Function DropDownUDF(iSource As Range) As Variant 'to delete and add validation in the specified range With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=" & iSource.Address .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With 'this will return the first value 'this reset the values when formula in sheet are refreshed DropDownUDF = VBA.Val(iSource(1)) End Function

• อย่ารันโค้ดนี้ บันทึก (Save) ไว้

• จากนั้น กลับไปที่เวิร์กชีต ที่สนใจ
• ต่อไป เลือกเซลล์ใดๆ ที่คุณต้องการสร้างรายการแบบเลื่อนลง (ในกรณีนี้คือ เซลล์ B11)
• ในเซลล์นั้น ให้เขียนฟังก์ชันที่เพิ่งสร้าง – DropDownUDF – เหมือนเขียนฟังก์ชันทั่วไป คือ ใส่ เครื่องหมายเท่ากับ (=) แล้วพิมพ์ชื่อฟังก์ชัน DropDownUDF และ ส่งอ้างอิงเซลล์ (B5:B9) เข้าไปในวงเล็บ

สูตรใน เซลล์ B11 จะเป็นแบบนี้:

• จากนั้น กด Enter

ผลลัพธ์คือ คุณจะได้ รายการแบบเลื่อนลงที่สร้างโดย UDF ใน เซลล์ B11 ด้วยค่า Grapes, Orange, Guava, Mango, Apple ที่เก็บไว้ในช่วง B5:B9 และที่เราส่งเข้าไปในฟังก์ชัน

อ่านเพิ่มเติม: วิธีใช้คำสั่ง IF ในสูตร Data Validation ใน Excel (6 วิธี)

6. ดึงข้อมูลจากชีตอื่นมาใส่ในรายการแบบเลื่อนลงด้วย VBA

ดูภาพด้านล่าง เรามีชุดข้อมูลในชีตชื่อ List

สิ่งที่เราจะทำคือ เราจะ สร้างรายการแบบเลื่อนลงในเซลล์ B5 ของชีตชื่อ Target (แสดงในภาพด้านล่าง) และค่าของรายการแบบเลื่อนลงนั้นจะเป็นค่าจากช่วง B5:B9 ของชีต List

มาดูขั้นตอนการทำด้วย VBA กัน

ขั้นตอน:

• ขั้นแรก เปิด Visual Basic Editor จากแท็บ Developer และ Insert Module ในหน้าต่างโค้ด
• จากนั้น คัดลอก โค้ดต่อไปนี้ และ วาง ลงในหน้าต่างโค้ด

Private Sub DropDownFromSheet() 'to store the dropdown list in cell B5 'you can replace "B5" with any other cell With Range("B5").Validation .Delete 'to extract data from "List" sheet and "B5:B9" range 'you can replace "=List!B5:B9" with your sheet name and range .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="=List!B5:B9" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End Sub

โค้ดพร้อมรันแล้ว

• ต่อไป Run แมโคร และดูภาพด้านล่างเพื่อดูผลลัพธ์

ผลลัพธ์จากการรันโค้ดสำเร็จ คือ มี รายการแบบเลื่อนลงสร้างในเซลล์ B5 ของชีต Target ด้วยค่า Grapes, Orange, Guava, Mango, Apple ที่เก็บอยู่ในช่วง B5:B9 ของสเปรดชีต List

อ่านเพิ่มเติม: วิธีใช้ Data Validation List จากชีตอื่น (6 วิธี)

7. ลบรายการแบบเลื่อนลง Data Validation ออกจาก Excel ด้วย VBA Macro

ส่วนนี้จะแสดงวิธี ลบรายการแบบเลื่อนลง ออกจาก Excel เราจะแสดงวิธี ลบรายการแบบเลื่อนลงที่มีอยู่ ใน เซลล์ B5 (แสดงในภาพด้านล่าง) ด้วย VBA macro

ขั้นตอนการทำให้ด้านล่าง

ขั้นตอน:

• ก่อนอื่น เปิด Visual Basic Editor จากแท็บ Developer และ Insert Module ในหน้าต่างโค้ด
• จากนั้น ในหน้าต่างโค้ด คัดลอก โค้ดต่อไปนี้ และ วาง

Sub DeleteDropDownList() Range("B5").Validation.Delete End Sub

โค้ดพร้อมรันแล้ว

• ต่อไป Run แมโคร และดูภาพด้านล่าง

จากภาพด้านบน จะเห็นว่า ไม่มีรายการแบบเลื่อนลง ใน เซลล์ B5 อีกแล้ว สุดท้าย เราได้เรียนรู้วิธี ลบรายการแบบเลื่อนลงที่มีอยู่ ออกจากสเปรดชีตด้วย VBA แล้ว

อ่านเพิ่มเติม: วิธีลบเซลล์ว่างจาก Data Validation List ใน Excel (5 วิธี)

สรุป

สรุปได้ว่า บทความนี้แสดง 7 การประยุกต์ใช้ที่แตกต่างกันของ รายการแบบเลื่อนลง Data Validation ใน Excel ด้วย VBA macro หวังว่าบทความนี้จะเป็นประโยชน์มากสำหรับคุณ รู้สึกสบายใจถามคำถามเกี่ยวกับหัวข้อนี้ได้เลยครับ

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

• [Fixed] Data Validation Not Working for Copy Paste in Excel (with Solution)
• วิธีใช้ Named Range สำหรับ Data Validation List ด้วย VBA ใน Excel
• Excel VBA สร้าง Data Validation List จาก Array
• วิธีใช้ Data Validation ใน Excel พร้อมสี (4 วิธี)
• ใช้ Data Validation หลายกฎในเซลล์เดียวใน Excel (3 ตัวอย่าง)