แก้ไขปัญหา Data Validation ไม่ทำงานเมื่อ Copy-Paste ใน Excel เพื่อการป้อนข้อมูลที่แม่นยำ

Excel Data Validation (การตรวจสอบความถูกต้องของข้อมูล) เป็นฟีเจอร์ที่ยอดเยี่ยมที่ควบคุมข้อมูลที่ป้อนลงในแผ่นงาน ในขณะป้อนข้อมูลใหม่ เราสามารถตั้งเงื่อนไขใดๆ ตามต้องการบน เซลล์ที่เลือกโดยใช้ Excel Data Validation แต่ปกติแล้ว Excel Data Validation Not Working Copy Paste (Data Validation ไม่ทำงานเมื่อคัดลอกวาง) เป็นปัญหาหลักในกระบวนการนี้

เพื่อความชัดเจนยิ่งขึ้น ผมจะใช้ ชุดข้อมูล (dataset) ของ บริษัท ที่มีข้อมูล ชื่อพนักงาน (Employee Name), แผนก (Department) และพนักงานที่อยู่ใน รายชื่อรอ (Waiting List)

ปัญหาและวิธีแก้ Data Validation ไม่ทำงานเมื่อ Copy Paste ใน Excel

1. เหตุผลที่ Data Validation ไม่ทำงานเมื่อ Copy Paste ใน Excel

ในชุดข้อมูลนี้ ผมจะใช้ฟีเจอร์ Data Validation ในคอลัมน์ ชื่อพนักงาน (Employee Name) เพื่อจำกัดการป้อนข้อมูล

ขั้นตอน:

• สำหรับนี้ ผมเลือก คอลัมน์ B ซึ่งมี ชื่อพนักงาน
• จากนั้น จากแท็บ Data ผมเลือก Data Tools และสุดท้ายเลือก Data Validation จากที่นั่น

กล่องโต้ตอบ (dialogue box) จะปรากฏ

• ที่นี่ แท็บ Settings (การตั้งค่า) เปิดอยู่ใน กล่องโต้ตอบ
• หลังจากนั้น เราต้องเลือก เกณฑ์การตรวจสอบ (Validation criteria) จาก Allow ที่นี่ ผมเลือก ความยาวข้อความ (Text length)
• ต่อไป ผมต้องจำกัดช่วงสำหรับ การตรวจสอบ (validation) ที่นี่ ผมอนุญาตให้ข้อมูลที่มีข้อความตั้งแต่ ขั้นต่ำ 1 ถึง สูงสุด 8 ตัวอักษร (characters) สำหรับการตรวจสอบ

ฟีเจอร์ Data Validation จะถูกนำไปใช้
ตามมา ผมป้อนข้อมูลที่ไม่ตรงตามเงื่อนไข ที่นี่ ผมพิมพ์ค่า Labuchange จาก Waiting List

ข้อความเตือน (warning message) จะแสดงสำหรับการป้อนข้อมูลที่ไม่ถูกต้อง เนื่องจากผมป้อนข้อมูลที่ไม่ถูกต้องตามเงื่อนไข Data Validation มันไม่ยอมรับค่า และ ข้อความเตือน ได้ปรากฏขึ้น

แต่ถ้าคุณ คัดลอก (copy) ค่านั้นและ วาง (paste) ในคอลัมน์ที่มีการตรวจสอบข้อมูล มันจะยอมรับค่า และ ข้อความเตือน จะไม่ปรากฏ

นี่เป็นปัญหาร้ายแรง เพราะ Data Validation ของเราไม่ทำงานเมื่อ copy paste

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

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

• วิธีใช้คำสั่ง IF ในสูตร Data Validation ใน Excel (6 วิธี)
• ใช้ Data Validation ใน Excel พร้อมสี (4 วิธี)
• วิธีใช้รายการตรวจสอบข้อมูลจากแผ่นงานอื่น (6 วิธี)
• Excel VBA สร้างรายการตรวจสอบข้อมูลจากอาร์เรย์
• วิธีใช้ Named Range สำหรับ Data Validation List กับ VBA ใน Excel

2. สร้าง Data Validation ด้วย VBA ให้ทำงานเมื่อ Copy Paste

ในกรณีของการแก้ปัญหา Excel Data Validation Not Working สำหรับ Copy Paste Visual Basic for Applications (VBA) เป็นวิธีแก้เพียงวิธีเดียว เราจะอธิบายวิธีแก้ที่นี่

ขั้นตอน:

• ขั้นแรก เลือกแท็บ Developer (นักพัฒนา)
• ต่อไป เลือก Visual Basic

หน้าต่าง ใหม่ จะปรากฏ

• คลิกที่ Sheet (แผ่นงาน) ที่คุณต้องการนำ โค้ด ไปใช้ ที่นี่ ผมเลือก Sheet2 ชื่อ VBA เพื่อใช้โค้ด

• เลือก Worksheet จาก General และ Change จาก Declarations เพื่อสร้าง Private Sub

• ตอนนี้ ใส่โค้ดต่อไปนี้ตามวิธีที่คุณต้องการตรวจสอบข้อมูล

โค้ดที่ผมใช้ระบุด้านล่าง:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ValidatedCells As Range
Dim Cell As Range
Set ValidatedCells = Intersect(Target, Target.Parent.Range("B:B"))
If Not ValidatedCells Is Nothing Then
    For Each Cell In ValidatedCells
        If Not Len(Cell.Value) <= 8 Then
            MsgBox "The Name """ & Cell.Value & _ 
            """ inserted in " & Cell.Address & _ 
            " in column B was longer than 8. Undo!", vbCritical
            Application.Undo
            Exit Sub
        End If
    Next Cell
End If
End Sub

ที่นี่ ผมสร้าง Private Sub ชื่อ Worksheet_Change ประกาศตัวแปร 2 ตัวคือ ValidatedCells และ Cell เป็น Range ต่อไป ผมใช้เมธอด Set เพื่อเก็บช่วงที่ต้องการนำ validation ไปใช้

จากนั้น เลือก คอลัมน์ B สำหรับ validation ผมยังระบุ ช่วง (range) ที่นี่โดยใช้เมธอด Range ผมใช้ลูป For ใน IF แบบซ้อน เพื่อตั้งเงื่อนไข ความยาวข้อความ (Text Length) ของช่วงที่เลือกไม่สามารถ ยาวกว่า 8 ได้ หากไม่ตรงกับช่วง กล่องข้อความเตือน (warning box) จะปรากฏเพื่อแสดงข้อความผ่าน MsgBox และตัวเลือก Undo จะพร้อมใช้งาน

• ตอนนี้ บันทึก (Save) โค้ด

• จากนั้น ตรวจสอบว่า validation ทำงานหรือไม่ จาก แผ่นงาน (sheet)

ที่นี่ ผมใส่ค่าจาก เซลล์ D7 โดย คัดลอก (copying) และ วาง (pasting) ใน B10 ค่าแสดง การแจ้งเตือนข้อผิดพลาด (Error alert) ตามเงื่อนไข data validation ดังนั้น กล่องข้อความเตือน (warning box) จะปรากฏ

วิธีนี้ยังทำงานได้อย่างสมบูรณ์หากผมป้อนข้อมูลผ่านแป้นพิมพ์หรือกระบวนการอื่นๆ

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

สมุดงานฝึกปฏิบัติ (Practice Workbook)

คุณสามารถฝึกปฏิบัติที่นี่เพื่อเป็นผู้เชี่ยวชาญ

สรุป

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

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

• Excel Data Validation Alphanumeric Only (Using Custom Formula)
• วิธีสร้างรายการแบบเลื่อนลง Excel สำหรับ Data Validation (8 วิธี)
• Data Validation Drop Down List with VBA in Excel (7 Applications)
• Autocomplete Data Validation Drop Down List in Excel (2 Methods)
• Excel Data Validation Drop Down List with Filter (2 Examples)