Thành thạo Excel VBA: Tạo danh sách thả xuống động với Phạm vi có tên

Nếu bạn đang tìm kiếm những cách dễ dàng nhất để sử dụng phạm vi có tên cho danh sách kiểm tra dữ liệu trong Excel VBA, thì bạn sẽ thấy bài viết này hữu ích. Phạm vi có tên rất hữu ích để sử dụng trong công thức kiểm tra dữ liệu để tạo danh sách thả xuống dễ dàng và nhiệm vụ này có thể được làm siêu dễ dàng với sự giúp đỡ của một số mã VBA.
Vì vậy, hãy bắt đầu bài viết chính của chúng ta để khám phá các cách sử dụng phạm vi có tên trong danh sách kiểm tra dữ liệu.

Tải xuống Sổ làm việc

4 Cách sử dụng Phạm vi có tên cho Danh sách Kiểm tra dữ liệu trong Excel với VBA

Ở đây, chúng ta có bộ dữ liệu sau chứa hồ sơ của một số sản phẩm và danh sách nhân viên bán hàng tương ứng. Sử dụng bộ dữ liệu này, chúng ta sẽ cố gắng chỉ ra các cách khác nhau với các mã VBA khác nhau để sử dụng phạm vi có tên trong danh sách kiểm tra dữ liệu.

Chúng tôi đã sử dụng phiên bản Microsoft Excel 365 ở đây, bạn có thể sử dụng bất kỳ phiên bản nào khác theo sự thuận tiện của bạn.

Phương pháp-1: Sử dụng Phạm vi có tên trong Kiểm tra dữ liệu để tạo Danh sách thả xuống

Ở đây, chúng ta đã đặt tên cho phạm vi cột Trái cây là Fruits và sử dụng mã VBA chúng ta sẽ tạo danh sách thả xuống trong ô D6.

Bước-01:
➤ Đi đến tab Nhà phát triển >> Tùy chọn Visual Basic.

Sau đó, Trình soạn thảo Visual Basic sẽ mở ra.
➤ Đi đến tab Chèn >> Tùy chọn Module.

Sau đó, một Module sẽ được tạo.

Bước-02:
➤ Viết mã sau

Sub Datavalidation1() Range("D6").Validation.Add Type:=xlValidateList, _ AlertStyle:=xlValidAlertStop, Formula1:="=Fruits" End Sub

Ở đây, Validation sẽ được thêm vào ô D6, xlValidateList dùng để tạo danh sách thả xuống và công thức được dùng làm tên của phạm vi "=Fruits".

➤ Nhấn F5 và sau đó nhấp vào biểu tượng thả xuống của ô D6.
Sau đó, bạn sẽ nhận được danh sách các loại trái cây và chọn bất kỳ mục nào từ danh sách như Cherry.

Cuối cùng, chúng ta đang nhận mục đã chọn của mình trong ô D6.

Đọc thêm: Cách tạo Danh sách Kiểm tra dữ liệu từ Bảng trong Excel (3 Phương pháp)

Phương pháp-2: Thêm Phạm vi có tên và Danh sách Kiểm tra dữ liệu với mã VBA

Chúng ta sẽ không tạo bất kỳ phạm vi có tên nào ở đây thủ công, mà một mã VBA đơn giản sẽ tạo ra một phạm vi có tên, và sau đó, sử dụng nó chúng ta sẽ nhận được danh sách thả xuống cuối cùng trong ô D6.

Các bước:
➤ Làm theo Bước-01 của Phương pháp-1.
➤ Viết mã sau

Sub Datavalidation2() ActiveWorkbook.Names.Add Name:="Fruit", _ RefersTo:=ThisWorkbook.Worksheets("Add").Range("B4:B10") Range("D6").Validation.Add Type:=xlValidateList, _ AlertStyle:=xlValidAlertStop, Formula1:="=Fruit" End Sub

Trước tiên, nó sẽ thêm tên Fruit vào phạm vi "B4:B10" của trang tính Add.
Sau đó, chúng ta sẽ thêm Validation vào ô D6, xlValidateList dùng để tạo danh sách thả xuống và công thức được dùng làm tên của phạm vi "=Fruit".

➤ Nhấn F5, sau đó, đi đến trang tính và nhấp vào biểu tượng thả xuống của ô D6.
Sau đó, bạn sẽ nhận được danh sách các loại trái cây và chọn bất kỳ mục nào từ danh sách như Blueberries.

Vì vậy, chúng ta đã có mục mong muốn Blueberries từ danh sách và bên cạnh đó, chúng ta có thể thấy phạm vi có tên đã tạo cho trái cây.

Nội dung liên quan: Tạo danh sách thả xuống Kiểm tra dữ liệu với chọn nhiều trong Excel

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

• Tự động hoàn thành Danh sách thả xuống Kiểm tra dữ liệu trong Excel (2 phương pháp)
• Danh sách thả xuống Kiểm tra dữ liệu Excel với Bộ lọc (2 ví dụ)
• Giá trị mặc định trong Danh sách Kiểm tra dữ liệu với Excel VBA (Macro và UserForm)
• Áp dụng Kiểm tra dữ liệu tùy chỉnh cho nhiều tiêu chí trong Excel (4 ví dụ)
• Kiểm tra dữ liệu Excel Chỉ chữ và số (Sử dụng công thức tùy chỉnh)

Phương pháp-3: Cập nhật Danh sách Kiểm tra dữ liệu với Phạm vi có tên sử dụng Excel VBA

Giả sử, chúng ta có danh sách thả xuống sau trong ô D6, hoạt động tốt cho tập dữ liệu cố định.

Nhưng, nếu chúng ta thêm một loại rau thêm Xà lách thì nó sẽ không xuất hiện trong danh sách thả xuống nghĩa là danh sách thả xuống của chúng ta không được cập nhật tự động trong trường hợp này.

Để cập nhật danh sách nhanh chóng và tự động bạn có thể làm theo phương pháp này.

3.1: Tạo Phạm vi có tên được cập nhật

Trước hết, chúng ta phải thêm tên cho phạm vi Cột B theo cách mà nó sẽ tự động đưa các mục mới thêm vào tên này.
➤ Đi đến tab Công thức >> Nhóm Tên đã định nghĩa >> Tùy chọn Quản lý tên.

Sau đó, hộp thoại Quản lý tên sẽ mở ra.
➤ Nhấp vào tùy chọn Mới.

Sau đó, trình thủ thuật Chỉnh sửa tên sẽ bật ra.
➤ Viết Rau trong hộp Tên và công thức sau trong hộp Tham chiếu đến và cuối cùng nhấn OK.

=OFFSET(Update!$B$4, 0, 0, COUNTA(Update!$B:$B)-2)

Ở đây, Update! Là tên trang tính, $B$4 là ô tham chiếu từ nơi chúng ta muốn di chuyển, 0 cho đối số Hàng và Cột có nghĩa là nó sẽ giữ nguyên vị trí tham chiếu hoặc vị trí bắt đầu.
COUNTA sẽ đếm số ô có bất kỳ loại giá trị nào trong Cột B và sau đó 2 sẽ bị trừ đi vì tiêu đề bộ dữ liệu ở B1 và tiêu đề cột ở B3. Vì vậy, bạn sẽ chỉ nhận được số ô chứa rau.
Số này sẽ là tham chiếu trả về từ vị trí bắt đầu và OFFSET sẽ luôn trả về phạm vi có tên được cập nhật ở đây.

Sau đó, bạn sẽ được đưa đến trình thủ thuật Quản lý tên.
➤ Nhấn Đóng.

3.2: Sử dụng mã VBA để áp dụng Danh sách Kiểm tra dữ liệu

➤ Nhấp chuột phải vào tên trang tính và chọn tùy chọn Xem mã.

Sau đó, cửa sổ mã sẽ xuất hiện.

➤ Gõ mã sau

Sub worksheet_Change(ByVal newitem As Range) Dim updatedrange, item If Not Intersect(newitem, Range("B:B")) Is Nothing Then For Each item In Range("Vegetables") updatedrange = updatedrange & "," & item Next item With ActiveSheet.Range("D6").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=updatedrange .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End If End Sub

Mã này sẽ thực thi chỉ nếu bất kỳ thay đổi giá trị hoặc thêm mới xảy ra và vì vậy chúng ta đã định nghĩa thủ tục là Worksheet_Change, Worksheet là Đối tượng và Change là Thủ tục.
newitem chứa địa chỉ ô mà chúng ta đang thêm giá trị mới và chúng ta định nghĩa nó là Range. Kiểu dữ liệu của updatedrange và item sẽ được coi là Variant, nơi chúng ta đã gán updatedrange cho phạm vi có tên được cập nhật của rau và item là cho giá trị của mỗi ô của phạm vi này.
Vòng lặp FOR sẽ gán phạm vi cập nhật cho updatedrange và câu lệnh WITH sẽ tránh lặp lại cùng một đối tượng, và cuối cùng, chúng ta đã thêm kiểm tra.

Bây giờ, đã đến lúc quay lại trang chính và kiểm tra hiệu ứng sau khi thêm sản phẩm Xà lách.
Như chúng ta thấy, chúng ta có mục mới này trong danh sách thả xuống của mình.

Sau khi chọn mục mới này, chúng ta đang có nó trong ô D6.

Đọc thêm: Cách sử dụng Danh sách Kiểm tra dữ liệu từ Trang tính khác (6 Phương pháp)

Phương pháp-4: Sử dụng Phạm vi có tên để tạo Danh sách thả xuống Có điều kiện

Ở đây, chúng ta sẽ tạo danh sách thả xuống trong ô E6 sẽ phụ thuộc vào điều kiện của ô D6 và vì lý do này chúng ta có hai phạm vi có tên sau, chẳng hạn như fruit1 và vegetable1.

Các bước:
➤ Làm theo Bước-01 của Phương pháp-1.
➤ Viết mã sau

Sub Datavalidation4() If Range("D6") = "Fruits" Then Range("E6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ Formula1:="=fruit1" Else Range("E6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ Formula1:="=vegetable1" End If End Sub

CẤU TRÚC IF-THEN sẽ kiểm tra nếu giá trị trong ô D6 là Fruits và đối với giá trị này, như một danh sách, chúng ta sẽ nhận được phạm vi có tên fruit1 như danh sách trong ô E6 ngược lại chúng ta sẽ nhận được phạm vi có tên vegetable1 như danh sách trong ô E6.

➤ Nhấn F5, sau đó đi đến trang tính và nhấp vào biểu tượng thả xuống của ô E6.
Sau đó, bạn sẽ nhận được danh sách trái cây cho danh mục là Fruits trong ô D6 và chọn bất kỳ mục nào từ danh sách như Blackberries.

Vì vậy, chúng ta đã có mục mong muốn Blackberries từ danh sách.

➤ Để thay đổi danh mục thành Rau chúng ta có thể thấy rằng chúng ta đang có danh sách rau sau khi chạy mã.

Sau khi chọn Bông cải xanh chúng ta đang nhận được mục này trong ô E6.

Nội dung liên quan: Cách sử dụng câu lệnh IF trong công thức Kiểm tra dữ liệu trong Excel (6 cách)

Phần thực hành

Để tự thực hành, chúng tôi đã cung cấp phần Thực hành như bên dưới trong trang tính tên Thực hành. Vui lòng tự làm.

Kết luận

Trong bài viết này, chúng tôi đã cố gắng bao quát các cách sử dụng phạm vi có tên cho danh sách kiểm tra dữ liệu trong Excel VBA dễ dàng. Hy vọng bạn sẽ thấy nó hữu ích. Nếu bạn có bất kỳ đề xuất hoặc câu hỏi nào, hãy thoải mái chia sẻ chúng trong phần bình luận.

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

• Cách sử dụng Kiểm tra dữ liệu trong Excel với Màu sắc (4 cách)
• Excel VBA tạo Danh sách Kiểm tra dữ liệu từ Mảng
• Cách sử dụng Công thức VLOOKUP tùy chỉnh trong Kiểm tra dữ liệu Excel
• [Đã sửa] Kiểm tra dữ liệu Không hoạt động cho Sao chép Dán trong Excel (có giải pháp)
• Cách loại bỏ ô trống khỏi Danh sách Kiểm tra dữ liệu trong Excel (5 phương pháp)