รายการตรวจสอบความถูกต้องของข้อมูลด้วย VBA ใน Excel (7 แอปพลิเคชัน)

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

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

คุณสามารถดาวน์โหลดเวิร์กบุ๊ก Excel แบบฝึกหัดฟรีได้จากที่นี่

7 วิธีกับ VBA ในรายการแบบเลื่อนลงการตรวจสอบความถูกต้องของข้อมูลใน Excel

ในส่วนนี้ คุณจะได้เรียนรู้ 7 แอปพลิเคชันต่างๆ ของ รายการแบบเลื่อนลงการตรวจสอบความถูกต้องของข้อมูล ใน Excel ด้วย VBA มาโคร

1. ฝัง VBA เพื่อสร้างรายการดรอปดาวน์การตรวจสอบความถูกต้องของข้อมูลใน Excel

หากต้องการทราบวิธีการสร้างรายการแบบเลื่อนลงสำหรับการตรวจสอบความถูกต้องของข้อมูลด้วย VBA , ทำตามขั้นตอนด้านล่าง.

ขั้นตอน:

• ที่จุดเริ่มต้น ให้กด Alt + F11 บนแป้นพิมพ์ของคุณหรือไปที่แท็บ นักพัฒนา -> Visual Basic เพื่อเปิด Visual Basic Editor .

• จากนั้น ในหน้าต่างรหัสป๊อปอัป จากแถบเมนู ให้คลิก แทรก -> โมดูล .

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

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

ตอนนี้รหัสของคุณพร้อมใช้งานแล้ว

โค้ดนี้จะสร้างรายการดรอปดาวน์ใน เซลล์ B5 . และรายการแบบเลื่อนลงจะมีค่า “องุ่น ส้ม ฝรั่ง มะม่วง แอปเปิ้ล “.

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

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

ดังที่เราเห็นจากภาพด้านบนคือมีรายการแบบเลื่อนลงที่สร้างขึ้นใน เซลล์ B5 ด้วยค่า “องุ่น ส้ม ฝรั่ง มะม่วง แอปเปิ้ล “.

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

2. การสร้างรายการแบบหล่นลงตามช่วงที่มีชื่อด้วย VBA ใน Excel

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

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

ขั้นตอน:

• ประการแรก เลือกช่วง โดยที่ค่าของรายการดรอปดาวน์มีอยู่ (ในกรณีของเรา ช่วงคือ B5:B9 )
• ประการที่สอง คลิกขวา ในช่วงที่เลือก
• รายการตัวเลือกจะปรากฏขึ้น จากนั้นเลือก กำหนดชื่อ…

• หลังจากนั้น ชื่อใหม่ กล่องป๊อปอัปจะปรากฏขึ้น ใน ชื่อ กล่องเขียน ชื่ออะไรก็ได้ ที่คุณชอบ (เราตั้งชื่อเซลล์ของเราว่า ผลไม้ )
• ภายหลัง คลิก ตกลง .

• เราตั้งชื่อช่วงสำเร็จแล้ว B5:B9 ผลไม้ (แสดงในภาพด้านล่าง)

ตอนนี้เราจะใช้ชื่อที่กำหนดไว้ในโค้ด VBA ของเรา . ขั้นตอนการดำเนินการดังแสดงด้านล่าง

• เหมือนเมื่อก่อน เปิด Visual Basic Editor จาก นักพัฒนา แท็บและ แทรก โมดูล ในหน้าต่างโค้ด
• จากนั้น ในหน้าต่างโค้ด คัดลอก รหัสต่อไปนี้และ วาง  มัน.

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

ตอนนี้รหัสของคุณพร้อมใช้งานแล้ว

โค้ดชิ้นนี้จะสร้างรายการดรอปดาวน์ใน เซลล์ B12 ด้วยค่า “องุ่น ส้ม ฝรั่ง มะม่วง แอปเปิ้ล ” ที่กำหนดไว้ในชื่อ ผลไม้ .

• ต่อมา เรียกใช้ มาโคร ผลลัพธ์จะปรากฏในภาพด้านล่าง

จากภาพด้านบนจะเห็นว่ามี dropdown list ที่สร้างใน Cell B12 ด้วยค่า “องุ่น ส้ม ฝรั่ง มะม่วง แอปเปิ้ล “.

อ่านเพิ่มเติม: สร้างรายการแบบเลื่อนลงสำหรับการตรวจสอบความถูกต้องของข้อมูลพร้อมการเลือกหลายรายการใน Excel

3. การสร้างช่องรายการตรวจสอบความถูกต้องของข้อมูลจากรายการที่กำหนดด้วยมาโคร

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

ขั้นตอน:

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

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

ตอนนี้รหัสของคุณพร้อมใช้งานแล้ว

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

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

จากภาพด้านบนจะเห็นว่ามี dropdown list ที่สร้างใน Cell B12 ด้วยค่า “องุ่น ส้ม ฝรั่ง มะม่วง แอปเปิ้ล ” ที่เราเก็บไว้ใน เซลล์ B5 ถึง B9 ในใบงาน

เนื้อหาที่เกี่ยวข้อง: ค่าเริ่มต้นในรายการตรวจสอบข้อมูลด้วย Excel VBA (มาโครและฟอร์มผู้ใช้)

การอ่านที่คล้ายกัน:

• รายการแบบเลื่อนลงการตรวจสอบความถูกต้องของข้อมูล Excel พร้อมตัวกรอง (2 วิธี)
• การตรวจสอบข้อมูลและตัวเลขเท่านั้น (โดยใช้สูตรที่กำหนดเอง)
• การตรวจสอบความถูกต้องของข้อมูล Excel ตามค่าของเซลล์อื่น
• วิธีใช้สูตร VLOOKUP แบบกำหนดเองในการตรวจสอบข้อมูล Excel

4. การใช้ VBA เพื่อสร้างรายการดรอปดาวน์หลายรายการใน Excel

คุณสามารถสร้าง รายการดรอปดาวน์หลายรายการ ด้วย VBA มาโคร มาดูวิธีการทำใน Excel กัน

ขั้นตอน:

• ขั้นแรก ให้เปิด Visual Basic Editor จาก นักพัฒนา แท็บและ แทรก โมดูล ในหน้าต่างโค้ด
• ประการที่สอง ในหน้าต่างโค้ด คัดลอก รหัสต่อไปนี้และ วาง  มัน.

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 .

• ประการที่สาม วิ่ง มาโคร ดู gif ด้านล่างเพื่อดูผลลัพธ์

แต่ละและทุกเซลล์ตั้งแต่ช่วง B5 ถึง B9 กำลังถือ รายการแบบเลื่อนลง .

อ่านเพิ่มเติม: ใช้การตรวจสอบความถูกต้องของข้อมูลที่กำหนดเองสำหรับหลายเกณฑ์ใน Excel (4 ตัวอย่าง)

5. การใช้ VBA เพื่อสร้างรายการดรอปดาวน์ด้วยฟังก์ชันที่ผู้ใช้กำหนด

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

ขั้นตอนในการรับที่แสดงด้านล่าง

ขั้นตอน:

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

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

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

• อย่าเรียกใช้รหัสนี้ บันทึก  มัน.

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

สูตรใน เซลล์ B11 จะเป็นดังนี้:

• จากนั้น กด Enter .

ด้วยเหตุนี้ คุณจะได้รับ รายการแบบเลื่อนลงที่สร้างโดย UDF ใน เซลล์ B11 ด้วยค่า “องุ่น ส้ม ฝรั่ง มะม่วง แอปเปิ้ล ” ที่จัดเก็บอยู่ในช่วง B5:B9 และที่เราส่งผ่านเข้าไปภายในฟังก์ชัน

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

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

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

สิ่งที่เราจะทำที่นี่คือ สร้างรายการแบบเลื่อนลงในเซลล์ B5 ของชีตชื่อ เป้าหมาย (shown in the picture below). And the values of that dropdown list will be the values from the range B5:B9 of the List แผ่น.

Let’s see the steps on how to do that with VBA .

ขั้นตอน:

• Firstly, open Visual Basic Editor จาก นักพัฒนา แท็บและ แทรก โมดูล ในหน้าต่างโค้ด
• Then, copy the following code and paste it into the code window.

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

ตอนนี้รหัสของคุณพร้อมใช้งานแล้ว

• Later, Run the macro and look at the following image to see the output.

As a result of the successful code execution, there is a dropdown list created in Cell B5 of the Target worksheet with the values “Grapes, Orange, Guava, Mango, Apple ” that are stored in range B5:B9 of the List spreadsheet.

อ่านเพิ่มเติม: วิธีใช้รายการตรวจสอบข้อมูลจากชีตอื่น (6 วิธี)

7. Deleting Data Validation Drop Down List from Excel with VBA Macro

This section will show you how to delete a dropdown list from Excel. We will show you how to delete the existing dropdown list in Cell B5 ( shown in the image below) with the VBA มาโคร

The steps to execute that are given below.

ขั้นตอน:

• At first, open Visual Basic Editor จาก นักพัฒนา แท็บและ แทรก โมดูล ในหน้าต่างโค้ด
• Then, in the code window, copy the following code and paste

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

ตอนนี้รหัสของคุณพร้อมใช้งานแล้ว

• Later, Run the macro and look at the following image.

As you can see from the above image is that there is no dropdown list anymore in Cell B5 . Finally, we have learnt how to delete an existing dropdown list from a spreadsheet with VBA .

อ่านเพิ่มเติม: How to Remove Blanks from Data Validation List in Excel (5 Methods)

บทสรุป

To conclude, this article showed you 7 different applications of the data validation drop-down list in Excel with the VBA macro . ฉันหวังว่าบทความนี้จะเป็นประโยชน์กับคุณมาก อย่าลังเลที่จะถามคำถามใด ๆ เกี่ยวกับหัวข้อนี้

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

• [แก้ไข] การตรวจสอบข้อมูลไม่ทำงานสำหรับการคัดลอกวางใน Excel (พร้อมโซลูชัน)
• วิธีใช้ช่วงที่มีชื่อสำหรับรายการตรวจสอบข้อมูลด้วย VBA ใน Excel
• Excel VBA เพื่อสร้างรายการตรวจสอบข้อมูลจากอาร์เรย์
• วิธีใช้การตรวจสอบความถูกต้องของข้อมูลใน Excel ด้วยสี (4 วิธี)
• ใช้การตรวจสอบความถูกต้องของข้อมูลหลายรายการในเซลล์เดียวใน Excel (3 ตัวอย่าง)