Cách tạo list box và combo box trong excel

Combobox là công cụ được sử dụng thường xuyên trong Userform khi thao tác trong VBA Excel, hỗ trợ hiển thị dữ liệu ở dạng danh sách nhưng bị ẩn đi, chỉ khi bạn nhấp vào nó thì dữ liệu được xổ ra. Trong bài viết này, benhvienranghammatsaigon.vn sẽ cùng bạn khám phá cách tạo Combobox có danh sách phụ thuộc nhé!

Biến Excel thành công cụ phân tích dữ liệu chuyên sâu.

You watching: Cách tạo list box và combo box trong excel

Hướng dẫn tạo Combobox có danh sách phụ thuộc

Sử dụng Combobox để phân cấp bậc danh sách

Chúng ta thực hiện phân cấp bậc danh sách để với mỗi đối tượng trong Combobox 2 sẽ thay đổi tương ứng với đối tượng trong Combobox 1.

Ví dụ:

*

Trong đó:

Trường: Combobox 1 - danh sách có cấp bậc lớn nhất, bậc 1

Lớp: Combobox 2 - danh sách có cấp bậc nhỏ hơn phụ thuộc vào nội dung của Combobox 1, bậc 2

Học sinh: Combobox 3 - danh sách có cấp bậc nhỏ nhất phụ thuộc vào nội dung của Combobox 2, bậc 3

Tạo Combobox tương ứng theo danh sách

Để tạo Combobox trong Exel theo các danh sách ta thực hiện các bước sau:

Bước 1: Trên thanh công cụ, chọn File và nhấp chọn Options

*

Bước 2: Tại thẻ Customize Ribbon, tích chọn Developer và nhấp OK

*

Như vậy, tab Developer sẽ hiện lên thanh công cụ

Developer
và chọn Insert

*

Bước 4: Chọn Form Controls là Combobox và thực hiên tạo Combobox tại nơi mà bạn muốn

*

Bước 5: Nhấp chuột phải vào Combobox vừa tạo và chọn Format Control

Bước 6: Cửa sổ Format Object hiện ra, tại thẻ Control và trong Input range chọn nút bên phải cạnh để chọn vùng dữ liệu muốn thêm vào Combobox.

*

Như vậy là bạn đã tạo được Combobox tương ứng với các danh sách rồi đấy!

*

*

Sử dụng VBA để tạo danh sách không trùng nhau trong Combobox

Để cho danh sách chọn ở các Combobox không có những giá trị trùng nhau ta thực hiện chạy VBA như sau:

Tạo danh sách không trùng nhau ở Combobox 1

Private Sub Worksheet_Activate() "Lấy danh sách không trùng vào ComboBox
Dim rng As Range "Tạo biến Vùng danh sách gốc
Dim r As Range "Tạo biến để thực hiện vòng lặp trong danh sách gốc
Dim Dic As Object "Tạo biến để gọi đối tượng dictionary
Dim ws As Worksheet "Tạo biến Sheet chứa danh sách gốc
Set ws = ActiveSheet "Sheet chứa danh sách gốc là sheet đang làm việc
Set rng = ws.Range("A2", ws.Range("A" & Rows.Count).End(xlUp)) "Vùng danh sách gốc nằm ở cột A, bắt đầu từ ô A2
Set Dic = CreateObject("scripting.dictionary") "Thiết lập đối tượng dictionary
Dic.CompareMode = vbTextCompare "Thực hiện việc so sánh các ký tự text trong thư viện dic
For Each r In rng
Dic(r.Value) = Empty "Các giá trị trùng sẽ được bỏ đi
Next
With ComboBox1
.ListFillRange = "" "Xóa danh sách cũ đã có trước đó
If .ListCount = 0 Then "Thực hiện việc nạp danh sách mới
.List = Application.Transpose(Dic.keys) "Lấy kết quả còn lại ở Dic vào Danh sách chọn của combobox1
.ListIndex = 0
End If
End With

End Sub

Đối với Combobox 2 và Combobox 3, khi tạo các danh sách không trùng nhau ta phải kết hợp với việc tạo điều kiện phụ thuộc lọc các giá trị trùng vì nó phụ thuộc vào các Combobox bậc trước đó.

See more: Máy Phay Cnc 5 Trục, 4 Trục, 3 Trục: Nên Sử Dụng Loại Máy Phay Nào?

Tạo điều kiện phụ thuộc nhau giữa các danh sách

Như ta đã biết, Combobox 2 phụ thuộc vào Combobox 1, vì vậy nội dung trong Combobox 2 thay đổi phụ thuộc vào sự thay đổi nội dung trong Combobox 1, theo ngôn ngữ của VBA là Combobox1_Change.

Tạo Combobox 2 phụ thuộc vào Combobox 1

Tại sheet chứa các Combobox, ta chọn đối tượng Combobox thay cho Worksheet và chọn Change cho Combobox đó. Như vậy,sự kiện Change là mặc địnhkhi đối tượng được chọn là Combobox.

See more: Hướng Dẫn Cách Tra Cước Điện Thoại Viettel Trả Sau Nhanh Và Chính Xác Nhất

*
Câu lệnh VBA:

Private Sub ComboBox1_Change() "Sự kiện thay đổi danh sách tại ComboBox2
Dim rng As Range "Tạo biến Vùng danh sách gốc
Dim r As Range "Tạo biến để thực hiện vòng lặp giúp kiểm tra danh sách nạp vào ComboBox2
Dim Dic As Object "Tạo biến thư viện để gọi đối tượng dictionary
Dim ws As Worksheet " Tạo biến Sheet chứa danh sách gốc
Set ws = ActiveSheet "Sheet chứa danh sách gốc là sheet đang làm việc (khi combobox đặt trong Sheet danh sách)
"Nếu khác combobox đặt tại sheet khác thì cần tham chiếu tới sheet chứa danh sách gốc
Set rng = ws.Range("A2", ws.Range("A" & Rows.Count).End(xlUp)) "Vùng danh sách gốc ở cột A, từ ô A2 tới dòng cuối có dữ liệu
Set Dic = CreateObject("scripting.dictionary") "Thiết lập đối tượng dictionary
Dic.CompareMode = vbTextCompare "Thực hiện việc so sánh các ký tự text trong thư viện dictionary
"Thực hiện vòng lặp để xét các nội dung được chọn ở cột danh sách bậc 1
For Each r In rng "Bắt đầu vòng lặp
If r = ComboBox1 Then "Nếu giá trị trong danh sách bậc 1 trùng với giá trị đã chọn ở ComboBox1
Dic(r.Offset(, 1).Value) = Empty "Những giá trị nào trùng lặp ở cột danh sách bậc 2 sẽ bị loại bỏ (DS bậc 2 cách DS bậc 1 là 1 cột)
End If
Next "Thực hiện vòng lặp đến hết các giá trị trong danh sách bậc 1
"Lấy các kết quả còn lại trong dictionary vào ComboBox2
With ComboBox2
.List = Application.Transpose(Dic.keys)
.ListIndex = 0
End With

End Sub

Tạo Combobox 3 phụ thuộc vào Combobox 2

Câu lệnh VBA:

Private Sub ComboBox2_Change() "Sự kiện thay đổi danh sách tại ComboBox3 theo giá trị được chọn tại ComboBox2
Dim rng As Range "Tạo biến Vùng danh sách gốc
Dim r As Range "Tạo biến để thực hiện vòng lặp giúp kiểm tra danh sách nạp vào ComboBox2
Dim Dic As Object "Tạo biến thư viện để gọi đối tượng dictionary
Dim ws As Worksheet " Tạo biến Sheet chứa danh sách gốc
Set ws = ActiveSheet "Sheet chứa danh sách gốc là sheet đang làm việc (khi combobox đặt trong Sheet danh sách)
"Nếu khác combobox đặt tại sheet khác thì cần tham chiếu tới sheet chứa danh sách gốc
"***
Set rng = ws.Range("B2", ws.Range("B" & Rows.Count).End(xlUp)) "Vùng danh sách gốc ở cột B, từ ô B2 tới dòng cuối có dữ liệu
"***
Set Dic = CreateObject("scripting.dictionary") "Thiết lập đối tượng dictionary
Dic.CompareMode = vbTextCompare "Thực hiện việc so sánh các ký tự text trong thư viện dictionary
"Thực hiện vòng lặp để xét các nội dung được chọn ở cột danh sách bậc 1
For Each r In rng "Bắt đầu vòng lặp
"***
If r = ComboBox2 Then "Nếu giá trị trong danh sách bậc 1 trùng với giá trị đã chọn ở ComboBox2
"***
Dic(r.Offset(, 1).Value) = Empty "Những giá trị nào trùng lặp ở cột danh sách bậc 3 sẽ bị loại bỏ (DS bậc 3 cách DS bậc 2 là 1 cột)
End If
Next "Thực hiện vòng lặp đến hết các giá trị trong danh sách bậc 2
"Khi đó những giá trị ở danh sách bậc 3 không cùng nhóm với giá trị được chọn ở danh sách bậc 2 cũng bị loại bỏ
"Lấy các kết quả còn lại trong dictionary vào ComboBox3
"***
With ComboBox3
"***
.List = Application.Transpose(Dic.keys)
.ListIndex = 0
End With
End Sub

Như vậy là bạn đã hoàn thành tạo Combobox có danh sách phụ thuộc rồi đó!

Kết luận

Ngay bây giờ hãy cùng luyện tậpthêm vớibộ bài tập Excel có đáp áncủa benhvienranghammatsaigon.vn để nằm lòngtin học văn phòng nhé!

Nhận tư vấn và đăng ký khóa họcở đây.

Bài viết tham khảo khác:

Hướng dẫn cách dùng hàm index trong Excel chi tiết nhất

Hướng dẫn cách copy chỉ những giá trị hiển thị sau khi lọc trong Excel

Hướng dẫn cách sắp xếp ngày trong Excel

Hướng dẫn cách dùng hàm VLOOKUP qua các ví dụ từ cơ bản đến nâng cao

Hướng dẫn cách sử dụng hàm IF với nhiều điều kiện: AND, OR, hàm IF lồng nhau và hơn thế