Hướng dẫn cách tạo Combobox trong Excel một cách nhanh chóng nhất

198 lượt xem
Share

Làm thế nào để tạo Combobox có danh sách phụ thuộc nhau một cách nhanh chóng nhất. Hãy đọc bài viết dưới đây do eLib biên soạn để tìm cho mình được câu trả lời chính xác nhất nhé.

Mục lục nội dung

Hướng dẫn cách tạo Combobox trong Excel một cách nhanh chóng nhất

1. Tạo combobox tương ứng theo các danh sách

Ở đây chúng ta có 3 danh sách nên sẽ cần tạo 3 combobox có tên lần lượt là Combobox1, Combobox2, Combobox3

Chúng ta sẽ sắp xếp thứ tự các combobox lần lượt như sau:

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

Combobox1 sẽ lấy danh sách Bộ phận

Combobox2 sẽ lấy danh sách Nhóm

Combobox3 sẽ lấy danh sách Nhân viên

2. Phân cấp bậc các danh sách theo combobox

Để hình dung về cấp bậc các danh sách, chúng ta xét ví dụ sau:

Phân cấp bậc các danh sách theo combobox

Trong hình trên chúng ta thấy: Mỗi nhân viên tại cột C sẽ nằm trong 1 nhóm cụ thể trong cột B, và mỗi nhóm lại thuộc một bộ phận nhất định trong cột A.

Như vậy có sự phân cấp bậc các danh sách như sau: Bộ phận > Nhóm > Nhân viên

  • Bộ phận là danh sách có cấp bậc lớn nhất, gọi là bậc 1
  • Nhóm là danh sách có cấp bậc nhỏ hơn bộ phận, phụ thuộc vào nội dung của Bộ phận, gọi là bậc 2
  • Nhân viên là danh sách có cấp bậc nhỏ nhất, phụ thuộc vào nội dung của Nhóm, gọi là bậc 3

Vậy mục tiêu chúng ta cần đạt được là:

  • Với mỗi bộ phận được chọn trong Combobox1 thì danh sách nhóm trong Combobox2 cần phải thay đổi tương ứng theo bộ phận đó.
  • Với mỗi nhóm được chọn trong Combobox2 thì danh sách nhân viên trong Combobox3 cần phải thay đổi tương ứng theo nhóm đó.

3. Tạo danh sách không trùng trong Combobox

Ở các danh sách Bộ phận và Nhóm chúng ta thấy có xuất hiện các giá trị trùng nhau. Do đó để cho danh sách chọn của combobox không xuất hiện các giá trị trùng này thì chúng ta có thể áp dụng cách làm tương tự ở bài trước để thực hiện.

Tạo danh sách không trùng trong Combobox1:

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

Từ các combobox2 và combobox3 chúng ta thấy nó phụ thuộc vào nội dung của combobox bậc trước nó, do đó chúng ta sẽ kết hợp việc tạo điều kiện phụ thuộc với việc lọc các giá trị trùng.

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

Như đã lập luận ở trên: “Với mỗi bộ phận được chọn trong Combobox1 thì danh sách nhóm trong Combobox2 cần phải thay đổi tương ứng theo bộ phận đó” => Như vậy việc thay đổi danh sách chọn trong combobox2 phụ thuộc vào việc nội dung trong combobox1 có thay đổi hay không. Điều này trong VBA được gọi là sự kiện Combobox1_Change

Tại Sheet chứa các combobox cần thực hiện (ở đây là Sheet3) chọn đối tượng Combobox thay cho Worksheet, tiếp đó chọn sự kiện Change cho combobox đó (sự kiện Change sẽ là mặc định khi chọn đối tượng Combobox)

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

Câu lệnh VBA được viết như sau:

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

Như vậy chúng ta đã có thể tạo được danh sách tại ComboBox2 phụ thuộc vào mỗi giá trị được chọn tại Combobox1 rồi.

Tiếp theo là để ComboBox3 phụ thuộc vào ComboBox2 thì chúng ta làm như sau:

  • Tạo sự kiện thay đổi ở combobox2: Private Sub ComboBox2_Change
  • Copy nội dung của Private Sub ComboBox1_Change và paste vào đây
  • Sửa các nội dung: Biến rng thay vì áp dụng Range cho cột A thì sửa thành cột B
  • Tại vòng lặp với biến r thì xét r = ComboBox2
  • Cuối cùng khi lấy dữ liệu vào Combobox thì đổi thành ComboBox3

Câu lệnh VBA như sau (những nội dung thay đổi cần chú ý được đặt trong phần dấu ***)

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 chúng ta đã hoàn thành được yêu cầu ban đầu rồi.

Một lưu ý là trong ComboBox đôi khi bị lỗi font chữ khi chúng ta chọn 1 giá trị tiếng việt có dấu. Để khắc phục điều này thì chúng ta có thể có 1 số cách sau:

  • Sử dụng tiếng việt không dấu
  • Tạo liên kết từ combobox ra 1 vị trí tại worksheet để có kết quả đúng (dù lỗi font hiển thị nhưng kết quả liên kết vẫn đúng)
  • Sử dụng bộ gõ khác (hết sức lưu ý vì có thể lỗi font khi gửi sang máy tính khác)

Như vậy bài viết đã hướng dẫn cho bạn cách tạo Combobox trong Excel một cách nhanh chóng nhất. Chỉ cẩn vài thao tác đơn giản, bạn đã có thể tạo Combobox rồi. Chúc các bạn thực hiện thao tác thành công!

198 lượt xem
Share
Ngày:13/08/2020 Chia sẻ bởi:
TẢI VỀ XEM ONLINE

CÓ THỂ BẠN QUAN TÂM