เชี่ยวชาญ Excel VBA: สร้าง Dropdown แบบไดนามิกด้วย Named Ranges

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

ดาวน์โหลดสมุดงาน (Download Workbook)

4 วิธีใช้ Named Range สำหรับ Data Validation List ใน Excel ด้วย VBA

ที่นี่ เรามีชุดข้อมูลต่อไปนี้ที่มีบันทึกของสินค้าบางอย่างและรายชื่อพนักงานขายของแต่ละสินค้า โดยใช้ชุดข้อมูลนี้ เราจะพยายามแสดงวิธีที่แตกต่างกันด้วย โค้ด VBA ที่แตกต่างกัน เพื่อใช้ named ranges ใน data validation list

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

วิธีที่ 1: ใช้ Named Range ใน Data Validation เพื่อสร้าง Dropdown List

ที่นี่ เราได้ตั้งชื่อช่วงของคอลัมน์ Fruits (ผลไม้) ว่า Fruits และใช้ โค้ด VBA เราจะสร้าง dropdown list ในเซลล์ D6

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

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

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

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

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

ที่นี่ Validation จะถูกเพิ่มเข้าไปในเซลล์ D6, xlValidateList ใช้สำหรับสร้าง dropdown list และสูตรถูกใช้เป็นชื่อของช่วง "=Fruits"

➤ กด F5 และต่อไป คลิกที่สัญลักษณ์ dropdown ของเซลล์ D6
จากนั้น คุณจะได้รายการของผลไม้ และเลือก任何一项 จากรายการ เช่น Cherries (เชอร์รี)

สุดท้าย เรากำลังได้รายการที่เลือกของเราในเซลล์ D6

อ่านเพิ่มเติม: วิธีสร้าง Data Validation List จากตารางใน Excel (3 วิธี)

วิธีที่ 2: เพิ่ม Named Range และ Data Validation List ด้วยโค้ด VBA

เราจะไม่สร้าง named range ที่นี่ด้วยตนเอง มันจะเป็น โค้ด VBA ง่ายๆ ที่จะสร้าง named range แล้วใช้มันเราจะได้ dropdown list สุดท้ายในเซลล์ D6

ขั้นตอน:
➤ ทำตาม ขั้นตอนที่ 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

ขั้นแรก มันจะเพิ่มชื่อ Fruit ให้กับช่วง "B4:B10" ของแผ่นงาน Add
จากนั้น เราจะเพิ่ม Validation ให้กับเซลล์ D6, xlValidateList สำหรับสร้าง dropdown list และสูตรใช้เป็นชื่อของช่วง "=Fruit"

➤ กด F5, จากนั้น ไปที่แผ่นงานและคลิกที่สัญลักษณ์ dropdown ของเซลล์ D6
ต่อไป คุณจะได้รายการของผลไม้ และเลือก任何一项 จากรายการ เช่น Blueberries (บลูเบอร์รี่)

ดังนั้น เราได้รายการที่ต้องการ Blueberries จากรายการ และนอกจากนี้ เรายังเห็น named range ที่สร้างขึ้นสำหรับผลไม้

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

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

• Autocomplete Data Validation Drop Down List in Excel (2 Methods)
• Excel Data Validation Drop Down List with Filter (2 Examples)
• Default Value in Data Validation List with Excel VBA (Macro and UserForm)
• Apply Custom Data Validation for Multiple Criteria in Excel (4 Examples)
• Excel Data Validation Alphanumeric Only (Using Custom Formula)

วิธีที่ 3: อัปเดต Data Validation List ด้วย Named Range โดยใช้ Excel VBA

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

แต่ ถ้าเราเพิ่มผักเพิ่มเติม Lettuce (ผักกาดหอม) มันจะไม่ปรากฏใน dropdown list ซึ่งหมายความว่า dropdown list ของเราไม่ได้อัปเดตโดยอัตโนมัติในกรณีนี้

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

3.1: สร้าง Named Range ที่อัปเดตได้

ขั้นแรก เราต้องเพิ่มชื่อสำหรับช่วงของ คอลัมน์ B โดยวิธีที่มันจะนำรายการที่เพิ่มใหม่เข้าไปในชื่อนี้โดยอัตโนมัติ
➤ ไปที่แท็บ Formulas (สูตร) >> กลุ่ม Defined Names (ชื่อที่กำหนด) >> ตัวเลือก Name Manager (ตัวจัดการชื่อ)

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

หลังจากนั้น วิซาร์ด Edit Name (แก้ไขชื่อ) จะปรากฏขึ้น
➤ เขียน Vegetables ในกล่อง Name และสูตรต่อไปนี้ในกล่อง Refers to และสุดท้ายกด OK

=OFFSET(Update!$B$4, 0, 0, COUNTA(Update!$B:$B)-2)

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

จากนั้น คุณจะถูกนำไปที่วิซาร์ด Name Manager
➤ กด Close (ปิด)

3.2: ใช้โค้ด VBA เพื่อนำ Data Validation List ไปใช้

➤ คลิกขวาที่ชื่อแผ่นงานและเลือกตัวเลือก View Code (ดูโค้ด)

หลังจากนั้น หน้าต่างโค้ดจะปรากฏ

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

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, Worksheet คือ Object และ Change คือ Procedure
newitem ประกอบด้วยที่อยู่ของเซลล์ที่เรากำลังเพิ่มค่าใหม่ และเรากำหนดมันเป็น Range ชนิดข้อมูลของ updatedrange และ item จะถูกพิจารณาเป็น Variant ซึ่งเรากำหนด updatedrange ให้เป็น named range ที่อัปเดตของผัก และ item สำหรับค่าของแต่ละเซลล์ของช่วงนี้
ลูป FOR จะกำหนดช่วงที่อัปเดตให้กับ updatedrange และคำสั่ง WITH จะหลีกเลี่ยงการทำซ้ำของออบเจ็กต์เดียวกัน และสุดท้าย เราได้เพิ่ม validation

ตอนนี้ ถึงเวลากลับไปที่แผ่นงานหลักและตรวจสอบผลหลังจากการเพิ่มสินค้า Lettuce
ตามที่เราเห็น เรามีรายการใหม่นี้ใน dropdown list ของเราตอนนี้

หลังจากเลือกตัวเลือกใหม่นี้ เรากำลังได้มันในเซลล์ D6

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

วิธีที่ 4: ใช้ Named Range สำหรับสร้าง Conditional Drop Down List (รายการแบบเลื่อนลงที่มีเงื่อนไข)

ที่นี่ เราจะสร้าง dropdown list ในเซลล์ E6 ซึ่งจะขึ้นอยู่กับเงื่อนไขของเซลล์ D6 และเพื่อเหตุผลนี้ เรามี named ranges 2 ตัว ดังนี้ fruit1 และ vegetable1

ขั้นตอน:
➤ ทำตาม ขั้นตอนที่ 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

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

➤ กด F5, จากนั้นไปที่แผ่นงานและคลิกที่สัญลักษณ์ dropdown ของเซลล์ E6
หลังจากนั้น คุณจะได้รายการของผลไม้สำหรับหมวดหมู่เป็น Fruits ในเซลล์ D6 และเลือก任何一项 จากรายการ เช่น Blackberries (แบล็คเบอร์รี่)

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

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

หลังจากเลือก Broccoli (บร็อคโคลี่) เรากำลังได้รายการนี้ในเซลล์ E6

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

ส่วนฝึกปฏิบัติ (Practice Section)

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

สรุป

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

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

• วิธีใช้ Data Validation ใน Excel พร้อมสี (4 วิธี)
• Excel VBA สร้าง Data Validation List จาก Array
• วิธีใช้ Custom VLOOKUP Formula ใน Excel Data Validation
• [Fixed] Data Validation Not Working for Copy Paste in Excel (with Solution)
• วิธีลบเซลล์ว่างจาก Data Validation List ใน Excel (5 วิธี)