Chiêu 38: Thêm dữ liệu vào danh sách Validation một cách tự động

Chiêu 38: Thêm dữ liệu vào danh sách Validation một cách tự độngNếu bạn đã từng sử dụng validation, bạn sẽ thấy đó là một tính năng rất hay. Có lẽ điểm ấn tượng nhất của nó chính là khả năng thêm một danh sách lựa chọn vào bất kỳ ô nào trên bảng tính và cho phép người sử dụng chọn lựa. Nó sẽ tuyệt hơn hay không nếu khi bạn nhập vào một tên mới trong một ô đang áp dụng tính năng validation thì Excel sẽ tự động thêm tên này vào trong danh sách validation? Điều...
Nội dung trích xuất từ tài liệu:
Chiêu 38: Thêm dữ liệu vào danh sách Validation một cách tự độngChiêu 38: Thêm dữ liệu vào danh sách Validation một cách tự độngNếu bạn đã từng sử dụng validation, bạn sẽ thấy đó là một tính năng rất hay. Có lẽ điểm ấn tượng nhất của nó chính làkhả năng thêm một danh sách lựa chọn vào bất kỳ ô nào trên bảng tính và cho phép người sử dụng chọn lựa. Nó sẽ tuyệthơn hay không nếu khi bạn nhập vào một tên mới trong một ô đang áp dụng tính năng validation thì Excel sẽ tự độngthêm tên này vào trong danh sách validation? Điều này có thể thực hiện được khi bạn làm theo các hướng dẫn trong bàinày.Giả sử bạn có danh sách tên trong vùng A1:A10 như hình sau:Danh sách này chính là tên của các nhân viên trong một công ty. Tại một ô đang áp dụng validation, bạn sẽ không thểnào nhập vào được tên một nhân viên mới ngoài danh sách lựa chọn, mà bạn phải thêm tên nhân viên mới này vào dòngcuối trong danh sách và điều chỉnh lại vùng dữ liệu của validation trước. Điều này sẽ rất bất tiện trong sử dụng.Để hạn chế nhược điểm này, tại ô A11 bạn nhập vào công thức bên dưới và sao chép đến A20 (dự trù trước sẽ thêm 10tên mới).=IF(OR($D$1=,COUNTIF($A$1:A10,$D$1)),x,$D$1)Chọn Formulas ➝ Defined Names ➝ Define Name (E2003: Insert ➝ Name ➝ Define), và nhập vào tên MyName tại hộpNames. Tại Refers To, bạn nhập vào công thức bên dưới rồi nhấp OK (E2003: nhấn Add ➝ OK).=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)Theo tôi, ta thay bằng công thức=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A)-COUNTIF(Sheet1!$A:$A,=x),1)Choạ ô D1, vào Data ➝ Data Tools ➝ Data Validation (E2003: Data ➝ Validation). Chọn List từ hộp Allow, và tạiSource nhập vào =MyNames, bạn đảm bảo rằng đã chọn thêm hộp In-Cell dropdown. Chọn ngăn Error Alert và bỏ chọnShow error alert after invalid data is entered. Nhấn nút OK khi hoàn tất.Nhấp phải chuột lên tên Sheet1 và chọn View Code. Sau đó bạn nhập vào đoạn mã sau:[highlight=VB] Private Sub Worksheet_Change(ByVal Target As Range)Dim lReply As LongIf Target.Cells.Count > 1 Then Exit SubIf Target.Address = $D$1 ThenIf IsEmpty(Target) Then Exit SubIf WorksheetFunction.CountIf(Range(MyNames), Target) = 0 ThenlReply = MsgBox(Add & Target & to list, vbYesNo + vbQuestion)If lReply = vbYes ThenRange(MyNames).Cells(Range(MyNames).Rows.Count + 1, 1) = TargetEnd IfEnd IfEnd IfEnd Sub[/highlight]Đóng cửa sổ VBE và lưa bảng tính lại, sau đó chọn ô D1 và nhập vào một tên mới chưa có trong danh sách validationvà nhấn Enter. Bạn sẽ thấy tên mới xuất xuất hiện trong danh sách dữ liệu trên bảng tính tại ô A11 và nếu chọn ô D1 vàmở danh sách validation bạn cũng sẽ thấy tên mới đã được bổ sung vào.Các bạn xem thêm một số cải tiến trong tập tin đính kèm.