7 danh sách thả xuống Xác thực dữ liệu nâng cao VBA cho Excel – Trở thành chuyên gia

Danh sách thả xuống xác thực dữ liệu là tính năng rất hữu ích cho nhiều tác vụ Excel. Triển khai VBA là phương pháp hiệu quả, nhanh chóng và an toàn nhất để chạy các thao tác trong Excel. Bài viết này giới thiệu 7 ứng dụng khác nhau của danh sách thả xuống xác thực dữ liệu trong Excel với macro VBA.

Tải xuống Workbook

Bạn có thể tải miễn phí tệp Excel thực hành tại đây.

7 Phương pháp VBA cho Danh sách thả xuống Xác thực dữ liệu trong Excel

Trong phần này, bạn sẽ học 7 ứng dụng khác nhau của danh sách thả xuống xác thực dữ liệu với VBA.

1. Nhúng VBA để tạo Danh sách thả xuống Xác thực dữ liệu trong Excel

Để biết cách tạo danh sách thả xuống xác thực dữ liệu bằng VBA, hãy làm theo các bước:

Các bước:

• Ban đầu, nhấn Alt + F11 hoặc vào tab Developer -> Visual Basic để mở Trình soạn thảo Visual Basic (VBE).

• Trong cửa sổ code, từ thanh menu, chọn Insert -> Module.

• Sao chép mã sau và dán vào cửa sổ code.

Sub CreateDropDownList() Range("B5").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="Grapes, Orange, Guava, Mango, Apple" End Sub

Mã đã sẵn sàng chạy.

Mã này tạo danh sách thả xuống tại Ô B5 với các giá trị Nho, Cam, Ổi, Xoài, Táo.

• Nhấn F5 hoặc chọn Run -> Run Sub/UserForm từ menu, hoặc click biểu tượng Run nhỏ trên thanh công cụ con.

Sau khi chạy, xem kết quả:

Như hình, danh sách thả xuống đã được tạo tại Ô B5 với các giá trị nêu trên.

Đọc thêm: Cách tạo danh sách thả xuống Excel cho Xác thực dữ liệu (8 cách)

2. Tạo danh sách thả xuống bằng Phạm vi đặt tên (Named Range) với VBA trong Excel

Khi không muốn ghi từng giá trị trong code, bạn có thể đưa tất cả tên vào một tên đã định nghĩa và sau đó dùng nó để gọi phạm vi giá trị. Đây là cách rất tiện lợi.

Phần này hướng dẫn tạo danh sách thả xuống từ danh sách cho trước dùng Named Range với VBA.

Các bước:

• Thứ nhất, chọn phạm vi chứa giá trị danh sách (trường hợp này là B5:B9).
• Thứ hai, chuột phải vào phạm vi đã chọn.
• Chọn Đặt tên... (Define Name...).

• Hộp thoại Tên mới (New Name) xuất hiện. Ô Tên (Name), đặt tên tùy ý (chúng tôi đặt Fruits).
• Nhấn OK.

• Phạm vi B5:B9 đã được đặt tên là Fruits (xem hình).

Bây giờ dùng tên đã định nghĩa trong code VBA. Các bước:

• Tương tự, mở VBE từ tab Developer và Insert một Module.
• Sao chép và dán mã sau:

Sub GenerateDropDownList() Range("B12").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=Fruits" End Sub

Mã sẵn sàng chạy.

Mã tạo danh sách thả xuống tại Ô B12 với giá trị từ tên Fruits.

• Chạy Run macro. Kết quả:

Danh sách thả xuống tại Ô B12 với các giá trị Nho, Cam, Ổi, Xoài, Táo.

Đọc thêm: Tạo danh sách thả xuống Xác thực dữ liệu với chọn nhiều trong Excel

3. Tạo hộp thả xuống Xác thực dữ liệu từ danh sách cho trước bằng Macro

Nếu bạn không thích Named Range, phần này dành cho bạn. Học cách tạo danh sách thả xuống từ phạm vi có sẵn trong dữ liệu.

Các bước:

• Mở VBE từ tab Developer và Insert Module.
• Sao chép và dán mã sau:

Sub ProduceDropDownList() With Range("B12").Validation .Add xlValidateList, xlValidAlertStop, xlBetween, "=$B$5:$B$10" .InCellDropdown = True End With End Sub

Mã sẵn sàng chạy.

Mã tạo danh sách thả xuống tại Ô B12 với giá trị từ phạm vi B5:B9.

• Chạy Run macro. Xem kết quả:

Danh sách thả xuống tại Ô B12 với giá trị từ Ô B5 đến B9.

Nội dung liên quan: Giá trị mặc định trong Danh sách xác thực với Excel VBA (Macro và UserForm)

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

• Danh sách thả xuống Xác thực dữ liệu Excel với Bộ lọc (2 phương pháp)
• Xác thực dữ liệu Chỉ chữ và số (Dùng công thức tùy chỉnh)
• Xác thực dữ liệu Excel dựa trên giá trị ô khác
• Cách dùng công thức VLOOKUP tùy chỉnh trong Xác thực dữ liệu Excel

4. Triển khai VBA để tạo nhiều Danh sách thả xuống trong Excel

Bạn có thể tạo nhiều danh sách thả xuống với VBA macro. Hãy xem cách làm.

Các bước:

• Mở VBE từ tab Developer và Insert Module.
• Sao chép và dán mã sau:

Sub MultipleDropDownList(iTarget As Range, iSource As Range) 'to delete and add validation in the target range With iTarget.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=" & iSource.Address .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End Sub Sub DropDownRange() MultipleDropDownList Sheet7.Range("B5:B10"), Sheet7.Range("A1:A3") End Sub

Mã sẵn sàng chạy.

Mã tạo danh sách thả xuống cho mỗi ô từ B5 đến B9.

• Chạy Run macro. Xem GIF kết quả:

Từng ô từ B5 đến B9 giờ đều có danh sách thả xuống.

Đọc thêm: Áp dụng Xác thực dữ liệu tùy chỉnh cho nhiều tiêu chí trong Excel (4 ví dụ)

5. Áp dụng VBA để tạo Danh sách thả xuống với Hàm do người dùng định nghĩa (UDF)

Bạn cũng có thể tạo danh sách thả xuống với hàm do người dùng định nghĩa (UDF) trong Excel.

Các bước:

• Ban đầu, chuột phải vào trang tính nơi bạn muốn triển khai UDF để tạo danh sách thả xuống.
• Chọn Xem mã (View Code) từ menu. Như hình, chúng tôi chuột phải vào trang tính tên UDF và chọn View Code.

• Sao chép mã sau và dán vào cửa sổ code tự động tạo.

Public Function DropDownUDF(iSource As Range) As Variant 'to delete and add validation in the specified range With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=" & iSource.Address .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With 'this will return the first value 'this reset the values when formula in sheet are refreshed DropDownUDF = VBA.Val(iSource(1)) End Function

• KHÔNG CHẠY mã này. LƯU (Save) nó.

• Quay lại trang tính cần dùng.
• Chọn bất kỳ ô nào muốn tạo danh sách thả xuống (ví dụ Ô B11).
• Trong ô đó, viết hàm mới tạo – DropDownUDF – như bất kỳ hàm nào: dấu =, tên hàm DropDownUDF, truyền tham chiếu ô (B5:B9) vào ngoặc.

Công thức tại Ô B11:

• Nhấn Enter.

Kết quả: Danh sách thả xuống do UDF tạo tại Ô B11 với giá trị từ phạm vi B5:B9.

Đọc thêm: Cách dùng câu lệnh IF trong công thức Xác thực dữ liệu Excel (6 cách)

6. Trích xuất dữ liệu từ trang tính khác vào Danh sách thả xuống với VBA

Xem hình sau. Chúng ta có dữ liệu trong trang tính tên List.

Mục tiêu: tạo danh sách thả xuống tại Ô B5 của trang tính Target (hình dưới). Giá trị lấy từ phạm vi B5:B9 của trang List.

Các bước với VBA:

Các bước:

• Mở VBE từ tab Developer và Insert Module.
• Sao chép và dán mã sau:

Private Sub DropDownFromSheet() 'to store the dropdown list in cell B5 'you can replace "B5" with any other cell With Range("B5").Validation .Delete 'to extract data from "List" sheet and "B5:B9" range 'you can replace "=List!B5:B9" with your sheet name and range .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="=List!B5:B9" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End Sub

Mã sẵn sàng chạy.

• Chạy Run macro. Kết quả:

Danh sách thả xuống tại Ô B5 của trang Target với giá trị từ B5:B9 của trang List.

Đọc thêm: Cách dùng Danh sách xác thực từ trang tính khác (6 phương pháp)

7. Xóa Danh sách thả xuống Xác thực dữ liệu khỏi Excel bằng Macro VBA

Phần này hướng dẫn xóa danh sách thả xuống khỏi Excel. Chúng ta xóa danh sách thả xuống hiện có tại Ô B5 (hình dưới) bằng VBA macro.

Các bước:

• Mở VBE từ tab Developer và Insert Module.
• Sao chép và dán mã sau:

Sub DeleteDropDownList() Range("B5").Validation.Delete End Sub

Mã sẵn sàng chạy.

• Chạy Run macro. Kết quả:

Không còn danh sách thả xuống tại Ô B5. Chúng ta đã học cách xóa danh sách thả xuống hiện có khỏi bảng tính bằng VBA.

Đọc thêm: Cách xóa khoảng trống khỏi Danh sách xác thực trong Excel (5 phương pháp)

Kết luận

Tóm lại, bài viết đã trình bày 7 ứng dụng khác nhau của danh sách thả xuống xác thực dữ liệu trong Excel với VBA macro. Hy vọng bài viết mang lại nhiều giá trị. Đừng ngần ngại đặt câu hỏi về chủ đề này.

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

• [Đã sửa] Xác thực dữ liệu không hoạt động khi Copy Paste trong Excel (có giải pháp)
• Cách dùng Phạm vi đặt tên cho Danh sách xác thực với VBA trong Excel
• Excel VBA tạo Danh sách xác thực từ Mảng
• Cách dùng Xác thực dữ liệu trong Excel với Màu sắc (4 cách)
• Áp dụng nhiều Xác thực dữ liệu trong một ô trong Excel (3 ví dụ)