Danh sách thả xuống phụ thuộc nhiều Excel VBA (3 cách)

Tạo một danh sách thả xuống phụ thuộc nhiều lần luôn là một thách thức trong MS Excel. Chúng tôi cần loại danh sách thả xuống này khi có sự phụ thuộc hoặc mối quan hệ giữa hai danh sách thả xuống. Chúng tôi thường tạo điều này bằng cách sử dụng các công thức khác nhau hoặc thay đổi các tùy chọn thích hợp trong Excel. Tuy nhiên, có một số cách dễ dàng để tạo nhiều danh sách thả xuống bằng cách sử dụng mã VBA trong Excel. Trong bài viết này, tôi sẽ chỉ ra nhiều cách khác nhau để tạo nhiều danh sách thả xuống bằng cách sử dụng mã VBA trong Excel.

Đọc thêm: Cách tạo danh sách thả xuống trong Excel (Độc lập và Phụ thuộc)

Danh sách thả xuống phụ thuộc trong Excel là gì?

Trước khi đi đến quy trình chính, hãy tìm hiểu danh sách thả xuống phụ thuộc là gì trong Excel. Khi có sự phụ thuộc giữa hai hoặc nhiều danh sách thả xuống thì chúng tôi gọi chúng là danh sách thả xuống phụ thuộc trong Excel. Hình ảnh dưới đây thể hiện khái niệm rõ ràng về danh sách thả xuống phụ thuộc.

Như chúng ta có thể thấy ở đây, hai danh sách thả xuống Danh mục và Thực phẩm hoàn toàn là hai danh sách thả xuống phụ thuộc. Tùy thuộc vào việc lựa chọn danh mục mà chúng tôi xác định danh sách thực phẩm ở đây. Do đó, cách nhiều danh sách thả xuống xếp tầng hoạt động.

Đọc thêm: Cách tạo danh sách thả xuống phụ thuộc động trong Excel

3 cách tạo nhiều danh sách thả xuống phụ thuộc Excel VBA

1. Cách thực hiện nhiều lựa chọn trong danh sách thả xuống trong Excel VBA

Giả sử chúng ta có hai danh sách có tên Tên dự án và Thành viên dự án. Đối với mỗi dự án, chúng tôi sẽ chỉ định một hoặc nhiều thành viên bằng cách sử dụng danh sách thả xuống.

Bước 1: Đi tới Nhà phát triển và mở Visual Basic (Phím tắt Alt + F11 )

Bước 2: Đi tới trang tính tương ứng từ menu VBAProject tương ứng.

Bước 3: Bây giờ hãy viết đoạn mã sau trong bảng điều khiển VBA

Mã:

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim Old_value As String
 Dim New_value As String
 Application.EnableEvents = True
 On Error GoTo Exitsub
 If Not Intersect(Target, Range("C4:C11")) Is Nothing Then
 If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
 GoTo Exitsub
 Else: If Target.Value = "" Then GoTo Exitsub Else
 Application.EnableEvents = False
 New_value = Target.Value
 Application.Undo
 Old_value = Target.Value
 If Old_value = "" Then
 Target.Value = New_value
 Else
 If InStr(1, Old_value, New_value) = 0 Then
 Target.Value = Old_value & ", " & New_value
 Else:
 Target.Value = Old_value
 End If
 End If
 End If
 End If
 Application.EnableEvents = True
Exitsub:
 Application.EnableEvents = True
End Sub

Bước 4: Bây giờ hãy chọn nhiều tên trong cột Thành viên Dự án

Bước 5: Tất cả các ô có thể thực hiện nhiều lựa chọn từ danh sách thả xuống

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

2. Tạo nhiều danh sách thả xuống phụ thuộc trong Excel VBA

Hãy có một tập dữ liệu về các loại thực phẩm khác nhau như rau, trái cây và các sản phẩm từ sữa. Bây giờ chúng tôi muốn tìm kiếm các mặt hàng thực phẩm theo danh mục của chúng. Giống như nếu chúng ta chọn danh mục là Trái cây thì trong cột Thực phẩm, các mục có sẵn sẽ là Mâm xôi, Mơ, Đào, Xoài. Vì vậy, các mặt hàng thực phẩm nên có sẵn theo danh mục. Có sự phụ thuộc giữa Danh mục và Thực phẩm.

Bước 1: Mở bảng điều khiển VBA bằng cách làm theo các bước tương tự từ phương pháp 1 ( Bước 1 và Bước 2 ) sau đó viết mã sau

Mã:

Để tạo danh sách thả xuống rau:

Sub Vegetable_List()
 
 Range("C4:C6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
 Formula1:="=Vegetable_List"
 
End Sub

Để tạo danh sách thả xuống trái cây:

Sub Fruit_List()
 
 Range("C4:C6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
 Formula1:="=Fruits_list"
 
End Sub

Để tạo danh sách thả xuống các sản phẩm từ sữa:

Sub Dairy_List()
 
 Range("C4:C6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
 Formula1:="=Dairy_Product_List"
 
End Sub

Trong phần này, chúng tôi sẽ tạo danh sách các mặt hàng thực phẩm riêng lẻ và lưu chúng trong danh sách thả xuống. Danh sách này sẽ có sẵn trong C4:C6 phạm vi.

Bước 2: Bây giờ chúng ta cần viết hàm chính cho dải ô B4:B6

Mã:

Private Sub Worksheet_Change(ByVal Target As Range)
 Range("B4:B6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
 Formula1:="Vegetable_List,Fruits_list,Dairy_Product_List"
 If Range("B4:B6").Value = "Vegetable_List" Then
 Call Vegetable_List
 ElseIf Range("B4:B6").Value = "Fruits_list" Then
 Call Fruit_List
 ElseIf Range("B4:B6").Value = "Dairy_Product_List" Then
 Call Dairy_List
 Else
 End If

Giải thích mã

• Tại đây, chúng tôi đang tạo một danh sách khác có tên cho danh mục trong B4:B6 phạm vi, sẽ chứa tên của danh mục thực phẩm.
• Sau đó, kiểm tra các giá trị của danh sách và phân loại chúng theo các mục của chúng. Đối với IF ELSE này câu lệnh được sử dụng.
• Nếu chúng tôi tìm thấy bất kỳ tên nào phù hợp, thì chúng tôi chỉ gọi hàm tạo danh sách bằng phương thức CallBack. Thích

If Range (“B4:B6”). Value =“Vegetable_List” Then

Gọi Vegetable_List

• Tại đây nếu các giá trị của ô khớp với Vegetable_List văn bản, sau đó chúng tôi sẽ gọi Vegetable_List chức năng tạo và hiển thị danh sách rau.

Vì vậy, mã đầy đủ của chúng tôi sẽ như thế này:

Bước 3: Bây giờ, hãy chuyển đến trang tính và chọn bất kỳ danh mục nào từ danh sách thả xuống

Bước 4: Sau đó, các mặt hàng liên quan sẽ có sẵn trên cột Thực phẩm

Bước 5: Kết quả cuối cùng sẽ như thế này:

3. Xóa nhiều danh sách thả xuống phụ thuộc trong Excel VBA

Trong phần trước, chúng ta đã chỉ thấy cách chúng ta có thể lấy danh sách phù hợp có liên quan trong Excel. Nhưng đôi khi có thể có một số lựa chọn không khớp sẽ không tự động bị xóa. Chúng tôi có thể lập công thức để ngăn chặn loại vấn đề này.

Một tùy chọn khác là sử dụng macro, để xóa ô phụ thuộc, sau khi chọn trong menu thả xuống đầu tiên. Điều đó sẽ ngăn các lựa chọn không khớp.

Bước 1: Mở bảng điều khiển VBA bằng cách làm theo các bước tương tự từ phương pháp 1 ( Bước 1 và Bước 2 ) sau đó viết mã sau

Mã:

Private Sub Worksheet_Change(ByVal Target As Range)
 On Error Resume Next
If Target.Column = 2 Then
 If Target.Validation.Type = 3 Then
 Application.EnableEvents = False
 Target.Offset(0, 1).ClearContents
 End If
End If

exitHandler:
 Application.EnableEvents = True
 Exit Sub
End Sub

Bước 2: Bây giờ, hãy chọn bất kỳ mục nào từ Thực phẩm và cố gắng chọn bất kỳ danh mục nào khác từ Danh mục và xem điều gì sẽ xảy ra

Đầu tiên

Thứ hai

Đầu ra cuối cùng

Đọc thêm: Cách xóa danh sách thả xuống trong Excel

Những điều cần nhớ

Kết luận

Đây là một số cách để tạo hoặc thao tác với nhiều danh sách thả xuống phụ thuộc Excel VBA. Tôi đã hiển thị tất cả các phương pháp với các ví dụ tương ứng của chúng nhưng có thể có nhiều lần lặp khác. Tôi cũng đã thảo luận về các nguyên tắc cơ bản của các chức năng được sử dụng. Nếu bạn có bất kỳ phương pháp nào khác để đạt được điều này, vui lòng chia sẻ với chúng tôi.

Bài đọc thêm

• Cách tạo danh sách thả xuống ở nhiều cột trong Excel (3 cách)
• Danh sách thả xuống của Excel tùy thuộc vào lựa chọn
• Cách sử dụng Câu lệnh IF để Tạo Danh sách Thả xuống trong Excel
• Tạo danh sách thả xuống từ một trang tính khác trong Excel (2 phương pháp)
• Cách chỉnh sửa danh sách thả xuống trong Excel (4 cách tiếp cận cơ bản)
• VLOOKUP với Danh sách thả xuống trong Excel