Tạo nút dropdown list có thêm chức năng tìm kiếm (search) trong excel

Do tính chất công việc mình thường xuyên dùng nút sổ xuống trong chức năng filter của Excel. Mình cũng muốn làm một nút như vậy, nhưng nút xổ xuống đó mình lại muốn có thêm chức năng search và link tới một ô nào đó nhằm mục đích link tới các dữ liệu khác ( Đối với nút xổ xuống nhưng không có chức năng search các bạn có thể tạo đơn giản bằng Data validation nhé). Thế là mài mò mãi các trang nước ngoài trên mạng cuối cùng cũng làm được. Cách tạo không khó đối với tất cả mọi người vì hầu hết chỉ sử dụng các công thức có sẵn của excel là chính. Giờ có thời gian mình cũng làm lại một video tiếng Việt để các bạn nào cần thì có thể ứng dụng vào công việc để công việc có thể dễ dàng  hơn. Các bạn tham khảo video bên dưới.

File excel mẫu: tại đây

Đầu tiên các bạn tạo thử một list danh sách mình cần. Sau đó có 3 việc các bạn cần làm:

• Bước 1: Tạo và tùy chỉnh nút dropdown list ( Mình tạm gọi là nút tìm kiếm)
• Bước 2: Cài đặt công thức cho dữ liệu
• Bước 3: Viết code VBA đơn giản cho nút combobox

Bước 1: Tạo và tùy chỉnh nút tìm kiếm     

Đầu tiên sẽ tạo nút combobox và cấu hình nó để khi search thì các từ đề xuất trong bản danh sách sẽ hiển thị theo. Các bạn tải file excel mẫu tại đây

Các bước tiến hành như sau:

          1.       Vào tab Developer Tab –> Insert –> ActiveX Controls –> Combo Box (ActiveX Control).

           2.       Chọn và kéo thả vào nơi thích hợp.

           3.       Sau đó click chuột phải và chọn property.

 

           4.       Trong bản property cho nút combobox, các bạn thay đổi các mục sau đây

ü  AutoWordSelect: False

ü  LinkedCell: D2

ü  ListFillRange: dropdownlist (Mình sẽ tạo một name range hay mình gọi là vùng dữ liệu với cái tên dropdownlist này trong bước 2, sau khi tạo được Name range thì mới có thể điền vào được)

ü  MatchEntry: 2 – fmMatchEntryNone

(LinkedCell: D, nghĩa là bất cứ những gì mình gõ trong ô combobox sẽ đều hiển thị trong ô B3, có thể dùng ô B3 này để lấy dữ liệu mình cần)

         5.       Chọn tab Developer -> tắt cái nút Design mode. Điều này sẽ cho phép mình gõ kí tự vào trong ô combobox và cũng cho phép ô D2 sẽ được link đồng thời cùng với ô Combobox.

Bước 2: Cài đặt công thức cho dữ liệu

Bây giờ thì ô combobox đã được cài đặt, với ý tưởng là khi mình gõ vào ô tìm kiếm thì đồng thời nó sẽ xổ ra một danh sách các từ gợi ý gần nhất. Để làm được như vậy, mình sẽ bắt đầu tạo 2 thứ:

• Tạo 3 cột phụ trợ.
• Tạo 1 ô công thức dữ liệu động cho nút combobox.

Tạo cột phụ trợ đầu tiên: Các bạn đặt công thức dưới đây trong ô I4 sao đó kéo xuống hết ô so với cột dữ liệu ban đầu.

=--ISNUMBER(IFERROR(SEARCH($D$2;G4;1);""))

Công thức này sẽ trả về giá trị 1 nếu ô dữ liệu có kí tự trùng với kí tự mình gõ trong ô search. Gỉa sử mình gõ kí tự “ph”

Tạo cột phụ trợ thứ 2: Tạo công thức dưới đây vào ô J4 và kéo xuống hết ô so với cột dữ liệu ban đầu:

=IF(I4=1;COUNTIF($I$4:I4;1);"")

Công thức này sẽ đếm số dữ liệu có kí tự giống với kí tự mình search và cộng dần lên.

      Cột phụ trợ thứ 3: Tạo công thức cho ô K4 và kéo xuống hết ô so với cột dữ liệu ban đầu:

=IFERROR(INDEX($G$4:$G$27;MATCH(ROWS($J$4:J4);$J$4:$J$27;0));"")

Công thức này sẽ lấy ô dữ liệu có kí tự trùng với kí tự mà mình search rồi đem sắp xếp lại gần nhau và lọa bỏ ô bị trống.

Tạo 1 ô dữ liệu động cho nút combobox:

Tại ô L4 gõ vào công thức dưới đây: 

=$K$4:INDEX($K$4:$K$27;COUNTIF($K$4:$K$27;"?*"))

Bây giờ tiếp tục mình sẽ tiếp tục tạo 1 dữ liệu động:

Trong bước 1, chúng ta vẫn chưa điền ListFillRange trong bảng property của nút combobox là dropdownlist. Bây giờ mình sẽ tạo tạo một cái Name Field có tên tương tự và sau khi tạo xong mình sẽ điền cái tên dropdownlist vào trong Listfillrange của bước 1.

 Chọn tab Formulas –> Name Manager.

1. Trong  Name Manager chọn New. Mở ra hộp thoại New Name
2. Trong The ô Name Field gõ dropdownlist
3. Trong ô Refers to Field gõ công thức sau:  =sheet1!$K$4:INDEX($K$4:$K$27;COUNTIF($K$4:$K$27;"?*"))

Bây giờ các bạn điền thêm vào trong Listfillrage trong hộp thoại property của nút combobox ở bước 1 bằng chữ dropdownlist.

Bước 3: Viết code VBA đơn giản cho nút combobox

Bước cuối cùng là viết code cho nút combobox ở trên, không quá phức tạo đâu nha các bạn hihi, để viết code mình sẽ làm như sau:

          1.       Chọn chuột phải trên thẻ worksheet đang làm -> chọn View code

          2.       Trong cửa sổ VBA các bạn dán code này vào:

Private Sub ComboBox1_Change()

ComboBox1.ListFillRange = "dropdownlist"

Me.ComboBox1.DropDown

End Sub

Vậy là xong rồi đó, các bạn lưu lại thành file excel có đuôi .xlsm nha.

*** Chú ý, trong bài này mình đang viết cho sheet 1, nút combo box tên là combobox1, nếu các bạn có nhiều combobox trong một sheet thì nhớ chọn đúng combobox để viết code VBA nha.

Mình viết bài này chủ yếu là giải trí và học tập.

Có gì thắc mắc cứ liên hệ với mình qua email: phong2000.sales@gmail.com.

Xin cảm ơn các bạn.