VBA để chọn giá trị từ danh sách thả xuống trong Excel (2 phương pháp)

Trình đơn thả xuống danh sách là một tính năng rất hữu ích để thực hiện các tác vụ khác nhau liên quan đến Excel. Triển khai VBA là phương pháp hiệu quả nhất, nhanh nhất và an toàn nhất để chạy bất kỳ thao tác nào trong Excel. Trong bài viết này, chúng tôi sẽ chỉ cho bạn 2 phương pháp hiệu quả để làm thế nào để chọn giá trị từ danh sách thả xuống trong Excel với macro VBA .

Tải xuống Workbook

Bạn có thể tải xuống sổ làm việc Excel thực hành miễn phí từ đây.

Tạo danh sách thả xuống từ danh sách chung trong Excel

Trước khi đi sâu vào phần viết mã, hãy tìm hiểu một cách rất đơn giản để tạo danh sách thả xuống từ danh sách chung trong Excel. Để sau này, chúng tôi có thể sử dụng danh sách thả xuống mà chúng tôi đã tạo như ví dụ của bài viết này.

Sau đây là danh sách chung mà chúng tôi có trong trang tính Excel của mình. Có giá trị lặp lại (ví dụ: Apple trong Ô B7 và B9 ) trong danh sách.

VBA để chọn giá trị từ danh sách thả xuống trong Excel (2 phương pháp)

Chúng ta sẽ xem cách tạo danh sách thả xuống bao gồm các giá trị (ví dụ: Nho, Cam, Táo, Xoài, Táo ) từ danh sách chung .

Các bước:

Lúc đầu, nhấp vào bất kỳ ô nào ( Ô D4 trong trường hợp của chúng tôi) nơi bạn muốn lưu danh sách thả xuống.
Sau đó, nhấp vào tab Dữ liệu .
Sau đó, chọn Xác thực dữ liệu từ Công cụ dữ liệu nhóm của dải băng.

VBA để chọn giá trị từ danh sách thả xuống trong Excel (2 phương pháp)

A Xác thực dữ liệu hộp bật lên sẽ xuất hiện. Từ đó,
- Chọn Danh sách trong Cho phép tiêu chí.
- Trong Nguồn tiêu chí, kéo phạm vi ( B5:B9 trong trường hợp của chúng tôi) có các giá trị cho danh sách thả xuống.

VBA để chọn giá trị từ danh sách thả xuống trong Excel (2 phương pháp)

Sau đó, nhấp vào OK .

Nhìn vào hình ảnh sau đây.

VBA để chọn giá trị từ danh sách thả xuống trong Excel (2 phương pháp)

Trong Ô D4 , có một danh sách thả xuống được tạo giữ các giá trị (ví dụ: Nho, Cam, Táo, Xoài, Táo ) được truy xuất từ danh sách chung (phạm vi B5:B9 ).

2 phương pháp với VBA để chọn giá trị từ danh sách thả xuống trong Excel

Trong phần này, bạn sẽ tìm hiểu cách chọn nhiều giá trị có cả giá trị lặp lại và không lặp lại từ danh sách thả xuống trong Excel với VBA .

1. Nhúng VBA để chọn nhiều giá trị từ Danh sách thả xuống trong Excel (với các giá trị lặp lại)

Chúng tôi có các giá trị lặp lại trong tập dữ liệu của mình. Nếu bạn muốn danh sách thả xuống của mình nắm bắt tất cả các giá trị bất kể giá trị đó có gấp đôi hay không , sau đó làm theo các bước bên dưới.

Các bước:

Lúc đầu, nhấn Alt + F11 trên bàn phím của bạn hoặc chuyển đến tab Nhà phát triển -> Visual Basic để mở Visual Basic Editor .

VBA để chọn giá trị từ danh sách thả xuống trong Excel (2 phương pháp)

Tiếp theo, nhấp chuột phải vào tên trang tính thích hợp và chọn Xem mã từ danh sách tùy chọn đã xuất hiện.

VBA để chọn giá trị từ danh sách thả xuống trong Excel (2 phương pháp)

Sau đó, sao chép mã sau và dán nó vào cửa sổ mã.

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
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

VBA để chọn giá trị từ danh sách thả xuống trong Excel (2 phương pháp)

Không chạy mã này, lưu nó.
Bây giờ quay lại trang tính lãi. Nếu bạn nhấp vào danh sách thả xuống đã tạo trong Ô D4 , bạn sẽ thấy bây giờ bạn có thể chọn nhiều giá trị từ trình đơn thả xuống (xem gif sau).

Như bạn có thể thấy từ gif ở trên, bạn thậm chí có thể chọn một giá trị cụ thể nhiều lần với VBA này mã số. Mã macro mà chúng tôi đã cung cấp trong phần này sẽ cho phép danh sách thả xuống chọn tất cả các loại giá trị .

Giải thích mã VBA

Dim ValueA As String
Dim ValueB As String

Xác định tên biến.

On Error GoTo Exitsub

Nếu xảy ra lỗi, hãy chuyển đến nhãn Exitsub .

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

Đặt điểm đến là ô D4 chứa xác thực dữ liệu. Nếu không có ô nào chứa xác thực dữ liệu, hãy chuyển đến nhãn Exitsub .

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

Nếu không có ô nào làm đích, thì hãy chuyển đến nhãn Exitsub . Nếu không, hãy thực hiện các dòng sau.

Application.EnableEvents = False

Tắt Sự kiện ứng dụng để Worksheet_Change macro có thể được ngăn chặn từ việc kích hoạt; Nếu không, nó có thể gây ra một vòng lặp vô hạn tiềm ẩn.

ValueB = Target.Value

Xác định Giá trịB là giá trị mới của ô đã thay đổi.

Application.Undo

Để hoàn tác ô đã thay đổi.

ValueA = Target.Value

Bằng cách hoàn tác thay đổi, giờ đây chúng tôi có thể xác định Giá trịA là giá trị cũ của ô đã thay đổi.

If ValueA = "" Then
 Target.Value = ValueB

Nếu giá trị cũ để trống, thì hãy lưu trữ giá trị mới làm đích.

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

Nếu không, hãy đặt cả giá trị cũ và giá trị mới là giá trị đích bằng cách nối chúng bằng dấu phẩy (, ). Đóng tất cả Nếu tuyên bố.

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

Bật lại Sự kiện ứng dụng.

Đọc thêm: Cách tạo danh sách thả xuống trong Excel với nhiều lựa chọn

Bài đọc tương tự:

Cách tạo danh sách thả xuống từ một trang tính khác trong Excel (2 phương pháp)
Tạo Danh sách Thả xuống Có thể Tìm kiếm trong Excel (2 Phương pháp)
Danh sách thả xuống của Excel không hoạt động (8 vấn đề và giải pháp)
Tạo Danh sách Thả xuống Excel từ Bảng (5 Ví dụ)
Tự động cập nhật danh sách thả xuống trong Excel (3 cách)

2. Áp dụng Macro VBA để Chọn nhiều giá trị từ Danh sách thả xuống (không có giá trị lặp lại)

Chúng tôi có các giá trị lặp lại trong tập dữ liệu của mình. Nếu bạn muốn danh sách thả xuống của mình bắt được tất cả các giá trị ngoại trừ các giá trị lặp lại , sau đó làm theo các bước bên dưới.

Các bước:

Như được hiển thị trước đây, hãy mở Visual Basic Editor từ Nhà phát triển tab.
Sau đó, chuyển đến cửa sổ mã từ Mã Chế độ xem tùy chọn xuất hiện bằng cách nhấp chuột phải trang tính quan tâm.
Sau đó, sao chép mã sau và dán nó vào cửa sổ mã của trang tính được 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
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

VBA để chọn giá trị từ danh sách thả xuống trong Excel (2 phương pháp)

Không chạy mã này, lưu nó.
Bây giờ quay lại trang tính lãi. Nếu bạn nhấp vào danh sách thả xuống đã tạo trong Ô D4 , bạn sẽ thấy bây giờ bạn có thể chọn nhiều giá trị từ trình đơn thả xuống (xem gif sau).

VBA để chọn giá trị từ danh sách thả xuống trong Excel (2 phương pháp)

Như bạn có thể thấy từ gif ở trên, bạn không thể chọn một giá trị cụ thể nhiều lần với VBA này mã số. Mã macro mà chúng tôi đã cung cấp trong phần này sẽ cho phép danh sách thả xuống chọn các giá trị mà không có bất kỳ giá trị lặp lại nào .

Giải thích mã VBA

Dim ValueA As String
Dim ValueB As String

Xác định tên biến.

Application.EnableEvents = True

Bật lại Sự kiện ứng dụng.

On Error GoTo Exitsub

Nếu xảy ra lỗi, hãy chuyển đến nhãn Exitsub .

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

Đặt điểm đến là ô D4 chứa xác thực dữ liệu. Nếu không có ô nào chứa xác thực dữ liệu, hãy chuyển đến nhãn Exitsub .

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

Nếu không có ô nào làm đích, thì hãy chuyển đến nhãn Exitsub . Nếu không, hãy thực hiện các dòng sau.

Application.EnableEvents = False

Tắt Sự kiện ứng dụng để Worksheet_Change macro có thể được ngăn chặn kích hoạt có thể gây ra một vòng lặp vô hạn tiềm ẩn.

ValueB = Target.Value

Xác định Giá trịB là giá trị mới của ô đã thay đổi.

Application.Undo

Để hoàn tác ô đã thay đổi.

ValueA = Target.Value

Bằng cách hoàn tác thay đổi, giờ đây chúng tôi có thể xác định Giá trịA là giá trị cũ của ô đã thay đổi.

If ValueA = "" Then
 Target.Value = ValueB

Nếu giá trị cũ để trống, thì hãy lưu trữ 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í của lần xuất hiện đầu tiên của một chuỗi con trong một chuỗi. Nếu đầu ra là 0 sau đó đặt cả giá trị cũ và giá trị mới là giá trị đích bằng cách nối chúng bằng dấu phẩy (, ).

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

Nếu không, hãy đặt giá trị cũ làm đích. Đóng tất cả Nếu tuyên bố.

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

Bật lại Sự kiện ứng dụng.

Đọc thêm: Danh sách thả xuống của Excel tùy thuộc vào lựa chọn

Kết luận

Để kết thúc, bài viết này đã chỉ cho bạn 2 phương pháp hiệu quả về cách chọn giá trị từ danh sách thả xuống trong Excel với macro VBA . Tôi hy vọng bài viết này rất hữu ích cho bạn. Vui lòng đặt bất kỳ câu hỏi nào liên quan đến chủ đề này.

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

Cách tạo danh sách thả xuống dựa trên công thức trong Excel (4 cách)
Danh sách thả xuống có điều kiện trong Excel (Tạo, Sắp xếp và Sử dụng)
Cách sử dụng Câu lệnh IF để Tạo Danh sách Thả xuống trong Excel
Liên kết giá trị ô với danh sách thả xuống trong Excel (5 cách)
Cách Loại bỏ Danh sách Thả xuống trong Excel