Cách chọn giá trị từ Drop-down List Excel bằng VBA (2 phương pháp chứng minh)

Danh sách thả xuống (Dropdown) là tính năng hữu ích cho nhiều tác vụ Excel. Triển khai VBA là phương pháp hiệu quả, nhanh và an toàn nhất để chạy thao tác trong Excel. Bài viết này trình bày 2 phương pháp hiệu quả để chọn giá trị từ danh sách thả xuống trong Excel bằng VBA macro.

Tải file thực hành

Bạn có thể tải file Excel thực hành miễn phí tại đây.

Tạo Dropdown List từ danh sách chung trong Excel

Trước khi vào phần code, hãy học cách đơn giản tạo dropdown list từ danh sách chung trong Excel. Sau đó dùng dropdown vừa tạo làm ví dụ.

Dưới đây là danh sách chung trong worksheet. Có giá trị lặp lại (ví dụ: Apple ở Ô B7 và B9).

Xem cách tạo dropdown list chứa các giá trị (ví dụ: Grapes, Orange, Apple, Mango, Apple) từ danh sách chung.

Các bước:

• Click vào ô bất kỳ (ví dụ Ô D4) nơi muốn đặt dropdown list.
• Chọn tab Data.
• Chọn Data Validation từ nhóm Data Tools trên Ribbon.

• Hộp thoại Data Validation hiện ra. Tại đó:
  • Chọn List trong Allow.
  • Trong Source, kéo chọn phạm vi (B5:B9 trong ví dụ) chứa giá trị cho dropdown.

• Nhấn OK.

Xem hình sau:

Ở Ô D4 có dropdown list chứa giá trị (ví dụ: Grapes, Orange, Apple, Mango, Apple) lấy từ danh sách chung (phạm vi B5:B9).

2 Phương pháp VBA chọn giá trị từ Drop Down List trong Excel

Phần này hướng dẫn chọn nhiều giá trị với cả giá trị lặp và không lặp từ dropdown list trong Excel bằng VBA.

1. Nhúng VBA chọn nhiều giá trị từ Drop Down List (có giá trị lặp)

Dữ liệu có giá trị lặp. Nếu muốn dropdown list nhận tất cả giá trị dù có trùng lặp, làm theo các bước:

Các bước:

• Nhấn Alt + F11 hoặc vào tab Developer -> Visual Basic để mở Visual Basic Editor.

• Chuột phải vào tên sheet phù hợp, chọn View Code.

• Sao chép mã sau và dán vào cửa sổ code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ValueA As String
    Dim ValueB As String
    On Error GoTo Exitsub
    If Target.Address = "$D$4" Then
        If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then GoTo Exitsub
        Else:
            If Target.Value = "" Then GoTo Exitsub
            Else
                Application.EnableEvents = False
                ValueB = Target.Value
                Application.Undo
                ValueA = Target.Value
                If ValueA = "" Then
                    Target.Value = ValueB
                Else
                    Target.Value = ValueA & ", " & ValueB
                End If
            End If
        End If
    End If
    Application.EnableEvents = True
Exitsub:
    Application.EnableEvents = True
End Sub

• KHÔNG chạy mã này, lưu lại.
• Quay lại worksheet. Click vào dropdown list đã tạo ở Ô D4, giờ có thể chọn nhiều giá trị từ dropdown (xem GIF dưới).

Như GIF trên, có thể chọn một giá trị cụ thể nhiều lần với VBA này. Mã cho phép dropdown list chọn mọi loại giá trị.

Giải thích mã VBA

Dim ValueA As String
Dim ValueB As String

Định nghĩa tên biến.

On Error GoTo Exitsub

Nếu lỗi, nhảy đến nhãn Exitsub.

If Target.Address = "$D$4" Then
    If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then GoTo Exitsub

Đặt đích là ô D4 có data validation. Nếu không có ô nào chứa validation, nhảy đến Exitsub.

Else: If Target.Value = "" Then GoTo Exitsub Else

Nếu không có ô đích, nhảy đến Exitsub. Ngược lại thực hiện các dòng sau.

Application.EnableEvents = False

Tắt Application Events để macro Worksheet_Change không tự kích hoạt gây vòng lặp vô tận.

ValueB = Target.Value

Gán ValueB = giá trị mới của ô thay đổi.

Application.Undo

Hoàn tác thay đổi ô.

ValueA = Target.Value

Sau Undo, gán ValueA = giá trị cũ của ô.

If ValueA = "" Then Target.Value = ValueB

Nếu giá trị cũ rỗng, lưu giá trị mới làm đích.

Else Target.Value = ValueA & ", " & ValueB End If End If End If

Ngược lại, gán cả giá trị cũ và mới làm đích bằng cách nối với dấu phẩy (,). Đóng các câu If.

Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True

Bật lại Application Events.

Đọc thêm: Cách tạo Drop Down List trong Excel cho phép chọn nhiều

Đọc tương tự:

• Cách tạo Drop Down List từ Sheet khác trong Excel (2 phương pháp)
• Tạo Searchable Drop Down List trong Excel (2 phương pháp)
• Excel Drop Down List không hoạt động (8 vấn đề và giải pháp)
• Tạo Excel Drop Down List từ Bảng (5 ví dụ)
• Tự động cập nhật Drop Down List trong Excel (3 cách)

2. Áp dụng VBA Macro chọn nhiều giá trị từ Drop Down List (KHÔNG giá trị lặp)

Dữ liệu có giá trị lặp. Nếu muốn dropdown list nhận tất cả giá trị TRỪ giá trị lặp, làm theo các bước:

Các bước:

• Mở Visual Basic Editor từ tab Developer như trước.
• Vào cửa sổ code từ View Code bằng chuột phải vào worksheet.
• Sao chép mã sau và dán vào code window của sheet chỉ định:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ValueA As String
    Dim ValueB As String
    Application.EnableEvents = True
    On Error GoTo Exitsub
    If Target.Address = "$D$4" Then
        If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then GoTo Exitsub
        Else:
            If Target.Value = "" Then GoTo Exitsub
            Else
                Application.EnableEvents = False
                ValueB = Target.Value
                Application.Undo
                ValueA = Target.Value
                If ValueA = "" Then
                    Target.Value = ValueB
                Else
                    If InStr(1, ValueA, ValueB) = 0 Then
                        Target.Value = ValueA & ", " & ValueB
                    Else:
                        Target.Value = ValueA
                    End If
                End If
            End If
        End If
    End If
    Application.EnableEvents = True
Exitsub:
    Application.EnableEvents = True
End Sub

• KHÔNG chạy mã này, lưu lại.
• Quay lại worksheet. Click vào dropdown list ở Ô D4, giờ có thể chọn nhiều giá trị từ dropdown (xem GIF).

Như GIF, KHÔNG thể chọn một giá trị cụ thể nhiều lần với VBA này. Mã cho phép dropdown list chọn giá trị không trùng lặp.

Giải thích mã VBA

Dim ValueA As String
Dim ValueB As String

Định nghĩa tên biến.

Application.EnableEvents = True

Bật Application Events.

On Error GoTo Exitsub

Nếu lỗi, nhảy đến Exitsub.

If Target.Address = "$D$4" Then
    If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then GoTo Exitsub

Đặt đích ô D4 có data validation. Nếu không có ô validation, nhảy đến Exitsub.

Else: If Target.Value = "" Then GoTo Exitsub Else

Nếu không có ô đích, nhảy đến Exitsub. Ngược lại thực hiện tiếp.

Application.EnableEvents = False

Tắt Application Events ngăn macro Worksheet_Change tự kích hoạt gây vòng lặp.

ValueB = Target.Value

Gán ValueB = giá trị mới ô thay đổi.

Application.Undo

Hoàn tác thay đổi ô.

ValueA = Target.Value

Sau Undo, gán ValueA = giá trị cũ ô.

If ValueA = "" Then Target.Value = ValueB

Nếu giá trị cũ rỗng, lưu giá trị mới làm đích.

Else If InStr(1, ValueA, ValueB) = 0 Then Target.Value = ValueA & ", " & ValueB

Hàm InStr trả về vị trí đầu tiên của chuỗi con trong chuỗi. Nếu kết quả 0 (chưa có), nối cả hai giá trị với dấu phẩy.

Else: Target.Value = ValueA End If End If End If End If

Ngược lại, giữ giá trị cũ. Đóng các câu If.

Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True

Bật lại Application Events.

Đọc thêm: Excel Drop Down List Depending on Selection

Kết luận

Tóm lại, bài viết trình bày 2 phương pháp hiệu quả cách chọn giá trị từ drop down list trong Excel với VBA macro. Hy vọng bài viết rất hữu ích. Mọi thắc mắc về chủ đề, hãy hỏi tự do.

Các bài viết liên quan

• Cách tạo Drop-Down List dựa trên công thức trong Excel (4 cách)
• Conditional Drop Down List trong Excel (Tạo, Sắp xếp và Sử dụng)
• Cách dùng IF Statement tạo Drop-Down List trong Excel
• Liên kết giá trị ô với Drop Down List trong Excel (5 cách)
• Cách xóa Drop-Down List trong Excel