Chiêu 38: Thêm dữ liệu vào danh sách Validation một cách tự động
Nế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ày có thể thực hiện được khi bạn làm theo các hướng dẫn trong bài
nà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òng
cuố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 10
tê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ộp
Names. 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ại
Source 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ọn
Show 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 Long
If Target.Cells.Count > 1 Then Exit Sub
If Target.Address = "$D$1" Then
If IsEmpty(Target) Then Exit Sub
If WorksheetFunction.CountIf(Range("MyNames"), Target) = 0 Then
lReply = MsgBox("Add " & Target & " to list", vbYesNo + vbQuestion)
If lReply = vbYes Then
Range("MyNames").Cells(Range("MyNames").Rows.Count + 1, 1) = Target
End If
End If
End If
End 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 validation
và 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.