วิธีใช้ช่วงที่มีชื่อสำหรับรายการตรวจสอบข้อมูลด้วย VBA ใน Excel

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

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

4 วิธีในการใช้ Named Range สำหรับรายการตรวจสอบข้อมูลใน Excel ด้วย VBA

ที่นี่ เรามีชุดข้อมูลต่อไปนี้ซึ่งมีบันทึกของผลิตภัณฑ์บางรายการและรายชื่อพนักงานขายที่เกี่ยวข้อง การใช้ชุดข้อมูลนี้ เราจะพยายามแสดงวิธีต่างๆ ด้วย VBA . ที่แตกต่างกัน รหัสเพื่อใช้ช่วงที่มีชื่อในรายการตรวจสอบข้อมูล

เราใช้ Microsoft Excel 365 เวอร์ชันที่นี่ คุณสามารถใช้เวอร์ชันอื่นได้ตามสะดวก

วิธีที่-1 :การใช้ Named Range ในการตรวจสอบข้อมูลเพื่อสร้างรายการแบบเลื่อนลง

ที่นี่ เราได้ตั้งชื่อช่วงของ ผลไม้ คอลัมน์ที่มี ผลไม้ และใช้ VBA รหัส เราจะสร้างรายการแบบเลื่อนลงในเซลล์ D6 .

ขั้นตอน-01 :
➤ ไปที่ นักพัฒนา แท็บ>> Visual Basic ตัวเลือก

จากนั้น ตัวแก้ไข Visual Basic จะเปิดขึ้น
➤ ไปที่ แทรก แท็บ>> โมดูล ตัวเลือก

หลังจากนั้น โมดูล จะถูกสร้างขึ้น

Step-02 :
➤ เขียนโค้ดต่อไปนี้

Sub Datavalidation1()

Range("D6").Validation.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, Formula1:="=Fruits"

End Sub

ที่นี่ การตรวจสอบความถูกต้อง จะถูกเพิ่มลงในเซลล์ D6 , xlValidateList ใช้สำหรับสร้างรายการแบบเลื่อนลงและใช้สูตรเป็นชื่อช่วง “=ผลไม้” .

➤ กด F5 และต่อมาคลิกที่สัญลักษณ์แบบเลื่อนลงของเซลล์ D6 .
จากนั้น คุณจะได้รับรายการผลไม้และเลือกรายการใดรายการหนึ่งจากรายการ เช่น เชอร์รี่ .

สุดท้าย เราได้รับรายการที่เลือกไว้ในเซลล์ D6 .

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

วิธีที่ 2 :การเพิ่ม Named Range และรายการตรวจสอบข้อมูลด้วยรหัส VBA

เราจะไม่สร้างช่วงที่มีชื่อใด ๆ ที่นี่ด้วยตนเอง แต่เป็น VBA . แบบง่าย โค้ดจะสร้างช่วงที่มีชื่อ จากนั้น เมื่อใช้มันเราจะได้รายการแบบเลื่อนลงในเซลล์ D6 .

ขั้นตอน :
➤ ปฏิบัติตาม Step-01 ของ วิธีที่-1 .
➤ เขียนโค้ดต่อไปนี้

Sub Datavalidation2()

ActiveWorkbook.Names.Add Name:="Fruit", _
RefersTo:=ThisWorkbook.Worksheets("Add").Range("B4:B10")
Range("D6").Validation.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, Formula1:="=Fruit"

End Sub

ประการแรกจะเพิ่มชื่อ ผลไม้ ถึงช่วง “B4:B10” ของแผ่นงาน เพิ่ม .
จากนั้นเราจะเพิ่ม การตรวจสอบ ไปที่เซลล์ D6 , xlValidateList ใช้สำหรับสร้างรายการแบบเลื่อนลงและสูตรที่ใช้เป็นชื่อช่วง “=Fruit” .

➤ กด F5 จากนั้นไปที่เวิร์กชีตและคลิกที่สัญลักษณ์แบบเลื่อนลงของเซลล์ D6 .
หลังจากนั้น คุณจะได้รับรายการผลไม้และเลือกรายการใดรายการหนึ่งจากรายการ เช่น บลูเบอร์รี่ .

เราได้ของที่ต้องการ บลูเบอร์รี่ จากรายการและนอกจากนี้ เราจะเห็นช่วงชื่อผลไม้ที่เราสร้างขึ้น

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

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

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

วิธีที่-3 :กำลังอัปเดตรายการตรวจสอบข้อมูลด้วยช่วงที่ตั้งชื่อโดยใช้ Excel VBA

สมมติว่า เรามีรายการดรอปดาวน์ต่อไปนี้ในเซลล์ D6 ซึ่งทำงานได้ดีสำหรับชุดข้อมูลคงที่

แต่ถ้าเราใส่ผักเพิ่ม ผักกาดหอม จากนั้นจะไม่ปรากฏในรายการแบบเลื่อนลงซึ่งหมายความว่ารายการแบบเลื่อนลงของเราจะไม่อัปเดตโดยอัตโนมัติในกรณีนี้

หากต้องการอัปเดตรายการอย่างรวดเร็วและอัตโนมัติ คุณสามารถทำตามวิธีนี้

3.1:การสร้างช่วงที่ตั้งชื่อที่อัปเดตแล้ว

อันดับแรก เราต้องเพิ่มชื่อให้กับช่วงของ Column ข ในลักษณะที่จะนำรายการที่เพิ่มเข้ามาใหม่เป็นชื่อนี้โดยอัตโนมัติ
➤ ไปที่ สูตร แท็บ>> ชื่อที่กำหนด กลุ่ม>> ตัวจัดการชื่อ ตัวเลือก

จากนั้น ตัวจัดการชื่อ กล่องโต้ตอบจะเปิดขึ้น
➤ คลิกที่ ใหม่ ตัวเลือก

หลังจากนั้น แก้ไขชื่อ ตัวช่วยสร้างจะปรากฏขึ้น
➤ จด ผัก ใน ชื่อ กล่องและสูตรต่อไปนี้ใน อ้างอิงถึง และสุดท้ายกด ตกลง .

ที่นี่ อัปเดต! เป็นชื่อแผ่นงาน $B$4 เป็นเซลล์อ้างอิงที่เราต้องการย้าย 0 สำหรับ แถว ​​ และ คอลัมน์ อาร์กิวเมนต์หมายความว่าจะยังคงอยู่ในการอ้างอิงหรือตำแหน่งเริ่มต้น
COUNTA จะนับจำนวนเซลล์ที่มีค่าประเภทใดก็ได้ใน คอลัมน์ B แล้วก็ 2 จะถูกลบเนื่องจากส่วนหัวของชุดข้อมูลใน B1 และส่วนหัวของคอลัมน์ใน B3 . ดังนั้นคุณจะได้จำนวนเซลล์ที่มีผักเท่านั้น
ตัวเลขนี้จะเป็นการอ้างอิงผลตอบแทนจากตำแหน่งเริ่มต้นและ OFFSET จะส่งคืนช่วงที่มีชื่อที่อัปเดตที่นี่เสมอ

จากนั้น คุณจะถูกนำไปที่ ตัวจัดการชื่อ ตัวช่วยสร้าง
➤ กด ปิด

3.2:การใช้รหัส VBA เพื่อใช้รายการตรวจสอบข้อมูล

➤ คลิกขวาที่ชื่อชีตและเลือก ดูโค้ด ตัวเลือก

หลังจากนั้น หน้าต่างรหัสจะปรากฏขึ้น

➤ พิมพ์โค้ดต่อไปนี้

Sub worksheet_Change(ByVal newitem As Range)

Dim updatedrange, item
If Not Intersect(newitem, Range("B:B")) Is Nothing Then
For Each item In Range("Vegetables")
updatedrange = updatedrange & "," & item
Next item
With ActiveSheet.Range("D6").Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
       xlBetween, Formula1:=updatedrange
      .IgnoreBlank = True
      .InCellDropdown = True
      .InputTitle = ""
      .ErrorTitle = ""
      .InputMessage = ""
      .ErrorMessage = ""
      .ShowInput = True
      .ShowError = True
End With
End If

End Sub

รหัสนี้จะดำเนินการก็ต่อเมื่อมีการเปลี่ยนแปลงค่าหรือการเพิ่มใด ๆ ดังนั้นเราจึงกำหนดขั้นตอนเป็น Worksheet_Change , ใบงาน เป็น วัตถุ และ เปลี่ยนแปลง คือ ขั้นตอน .
รายการใหม่ มีที่อยู่ของเซลล์ที่เรากำลังเพิ่มค่าใหม่และเรากำหนดให้เป็น ช่วง . ประเภทข้อมูลของ ช่วงที่อัปเดต  และ รายการ  จะถือว่าเป็น Variant ซึ่งเราได้กำหนด ช่วงที่อัปเดต   ไปที่ชื่อช่วงที่อัปเดตของผักและ รายการ   เป็นค่าของแต่ละเซลล์ในช่วงนี้
สำหรับ loop จะกำหนดช่วงที่อัปเดตให้กับ updatedrange   และ ด้วย คำสั่งจะหลีกเลี่ยงการทำซ้ำของวัตถุเดียวกัน และสุดท้าย เราได้เพิ่ม การตรวจสอบ .

ตอนนี้ได้เวลากลับไปที่หน้าหลักและดูเอฟเฟกต์หลังจากเพิ่มผลิตภัณฑ์ ผักกาดหอม .
อย่างที่เราเห็น เรามีรายการใหม่นี้ในรายการแบบเลื่อนลง

หลังจากเลือกรายการใหม่นี้แล้ว เราจะมีรายการดังกล่าวในเซลล์ D6 .

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

วิธีที่-4 :การใช้ Named Range เพื่อสร้างรายการดรอปดาวน์แบบมีเงื่อนไข

ที่นี่ เราจะสร้างรายการแบบเลื่อนลงในเซลล์ E6 ซึ่งจะขึ้นอยู่กับสภาพของเซลล์ D6 และด้วยเหตุนี้ เราจึงมีช่วงชื่อสองช่วงดังต่อไปนี้ เช่น ผลไม้1 และ ผัก1 .

ขั้นตอน :
➤ ปฏิบัติตาม Step-01 ของ วิธีที่-1 .
➤ เขียนโค้ดต่อไปนี้

Sub Datavalidation4()

If Range("D6") = "Fruits" Then
Range("E6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:="=fruit1"
Else
Range("E6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:="=vegetable1"
End If

End Sub

ถ้า-แล้ว คำสั่งจะตรวจสอบว่าค่าในเซลล์ D6 คือ ผลไม้ และสำหรับค่านี้ เราจะได้ชื่อ range fruit1 . เป็นรายการ เป็นรายการในเซลล์ E6 มิฉะนั้นเราจะได้ชื่อช่วง vegetable1 เป็นรายการในเซลล์ E6 .

➤ กด F5 จากนั้นไปที่เวิร์กชีตและคลิกที่สัญลักษณ์แบบเลื่อนลงของเซลล์ E6 .
หลังจากนั้นจะได้รายชื่อผลไม้สำหรับหมวดเป็น ผลไม้ ในเซลล์ D6 และเลือกรายการใดก็ได้จากรายการ เช่น Blackberries .

เราได้ไอเทมที่ต้องการ Blackberries จากรายการ

➤ สำหรับเปลี่ยนหมวดเป็น ผัก เราจะเห็นว่าเรามีรายการผักหลังจากรันโค้ดแล้ว

หลังจากเลือก บร็อคโคลี่ . แล้ว เราได้รับไอเทมนี้ในเซลล์ E6 .

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

ภาคปฏิบัติ

สำหรับการทำแบบฝึกหัดด้วยตัวเองเราได้จัดเตรียมแบบฝึกหัด ส่วนด้านล่างในชีตชื่อ ฝึกปฏิบัติ . กรุณาทำด้วยตัวเอง

บทสรุป

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

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

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