caohung841
Tổng số bài gửi : 159
Điểm tích lũy : 4877
Thích : 2
Tham gia : 10/02/2012
 |  Tiêu đề: Thêm dữ liệu vào danh sách Validation một cách tự động  Mon Feb 27, 2012 12:01 pm | |
| 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.
MỤC LỤC Những tuyệt chiêu trong Excel
 Tập tin đính kèm
Hack38.zip
(11.6 KB, 2702 lần tải)
| |
|
