Bài viết: Chiêu 42: Giảm danh sách xổ xuống của Validation, sau khi chọn 1

[ptm0412]

Chiêu 42: Giảm danh sách xổ xuống của Validation, sau khi chọn 1

Rất hữu ích cho người dùng Excel khi bạn cho họ 1 danh sách để chọn bằng Validation, nhưng cái danh sách này tự động ngắn lại mỗi khi họ chọn 1 mục trong danh sách, để khỏi phải lựa chọn trong 1 danh sách quá dài, đồng thời tránh việc nhập trùng dữ liệu (đôi khi cần thiết).

Bước 1:
Trong 1 sheet bất kỳ (thí dụ sheet1) bạn có 1 danh sách trong vùng A1:A10. Bạn hy đặt name cho danh sách này bằng cách tô chọn vùng danh sách, rồi gõ tên MyList trong hộp namebox.

Bước 2:
Trong 1 sheet khác mà bạn muốn dùng validation dựa vào danh sách MyList nói trên, tô chọn vùng bạn cần, mở menu Data - Validation, chọn Allow là List, source là =MyList, rồi nhấn OK.

Bước 3: mở cửa sổ VBA của sheet đó, copy đoạn code sau:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strVal As String
    Dim strEntry As String
    On Error Resume Next
    strVal = Target.Validation.Formula1
    If Not strVal = vbNullString Then
        strEntry = Target
        Application.EnableEvents = False
        With Sheet1.Range("MyList")
            .Replace What:=strEntry, _
            Replacement:="", LookAt:=xlWhole, _
            SearchOrder:=xlByRows, MatchCase:=False
            .Sort Key1:=.Range("A1"), Order1:=xlAscending, _
            Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
            Orientation:=xlTopToBottom
            .Range("A1", .Range("A65536").End(xlUp)).Name = "MyList"
        End With
    End If
    Application.EnableEvents = True
    On Error GoTo 0
End Sub

Đóng cửa sổ VBA rồi quay về Excel. Thử chọn 1 mục trong validation, sang 1 ô khác và xổ cái validation xuống, bạn sẽ thấy mục vừa chọn xong không còn trong list nữa.
Lưu ý trong code, khi nói đến name MyList, phải xác định rõ Sheet1.MyList, vì nếu không, VBA sẽ cho rằng name này nằm chung sheet với sheet chứa code và sẽ không tìm thấy và báo lỗi.

​

MỤC LỤC Những tuyệt chiêu trong Excel
​

Một số bài viết có liên quan:
1/ Chiêu 41: Bẫy lỗi để trống dữ liệu
2/ Chiêu 40: Cho phép sử dụng tính năng Group and Outline trên bảng tính bị khoá
3/ Chiêu thứ 39: Ngày tháng trong Excel
4/ Chiêu 38: Thêm dữ liệu vào danh sách Validation một cách tự động
5/ Chiêu 37: Chuyển đổi các hàm và công thức trong Excel thành giá trị
6/ Chiêu thứ 36: Làm nổi các Subtotal của Excel
7/ Chiêu 35: Tự tạo danh sách để fill
8/ Chiêu 33: Tạo các định dạng số cho riêng bạn
9/ Chiêu 32: Thao tác trên dữ liệu với Advanced Filter
10/ Chiêu thứ 31: Sắp xếp ngẫu nhiên