Cách sử dụng dải ô đã đặt tên cho danh sách xác thực dữ liệu với VBA trong Excel

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

Tải xuống Workbook

4 cách sử dụng dải ô đã đặt tên cho danh sách xác thực dữ liệu trong Excel với VBA

Tại đây, chúng tôi có tập 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 của chúng. Sử dụng tập dữ liệu này, chúng tôi sẽ cố gắng hiển thị các cách khác nhau với các VBA khác nhau mã để sử dụng các dải ô đã đặt tên trong danh sách xác thực dữ liệu.

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

Phương pháp-1 :Sử dụng Dải ô đã Đặt tên trong Xác thực Dữ liệu để Tạo Danh sách Thả xuống

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

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

Sau đó, Trình chỉnh sửa Visual Basic sẽ mở ra.
➤ Đi tới Chèn Tab>> Mô-đun Tùy chọn.

Sau đó, một Mô-đun sẽ được tạo.

Bước-02 :
➤ Viết đoạn mã sau

Sub Datavalidation1()

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

End Sub

Đây, Xác thực sẽ được thêm vào ô D6 , xlValidateList là để tạo danh sách thả xuống và công thức được sử 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 một mục bất kỳ từ danh sách như Anh đào .

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

Đọc thêm:Cách tạo danh sách xác thực dữ liệu từ bảng trong Excel (3 phương pháp)

Phương pháp-2 :Thêm Dải ô được Đặt tên và Danh sách Xác thực Dữ liệu bằng Mã VBA

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

Các bước :
➤ Thực hiện theo Bước-01 của Phương pháp-1 .
➤ Viết đoạn 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

Đầu tiên, nó sẽ thêm tên Fruit đến phạm vi “B4:B10” của trang tính Thêm .
Sau đó, chúng tôi sẽ thêm Xác thực vào ô D6 , xlValidateList là để tạo danh sách thả xuống và công thức được sử dụng làm tên của phạm vi “=Fruit” .

➤ Nhấn F5 , sau đó, chuyển đế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 một mục bất kỳ từ danh sách như Quả việt quất .

Vì vậy, chúng tôi đã có mặt hàng Quả việt quất mong muốn của chúng tôi từ danh sách và bên cạnh đó, chúng tôi có thể thấy phạm vi được đặt tên đã tạo của chúng tôi cho các loại trái cây.

Nội dung có liên quan:Tạo danh sách thả xuống xác thực dữ liệu với nhiều lựa chọn trong Excel

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

• Danh sách thả xuống xác thực dữ liệu tự động hoàn thành trong Excel (2 phương pháp)
• Danh sách thả xuống xác thực dữ liệu Excel có bộ lọc (2 ví dụ)
• Giá trị Mặc định trong Danh sách Xác thực Dữ liệu với Excel VBA (Macro và UserForm)
• Á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ụ)
• Chỉ xác thực dữ liệu Excel bằng 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 xác thực dữ liệu với phạm vi được đặt tên bằ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 bổ sung thêm một loại rau Xà lách thì nó sẽ không xuất hiện trong danh sách thả xuống có nghĩa là danh sách thả xuống của chúng tôi không được cập nhật tự động trong trường hợp này.

Để cập nhật danh sách một cá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 tên được cập nhật

Trước tiên, chúng tôi phải thêm tên cho phạm vi Cột B theo cách mà nó sẽ tự động lấy các mục mới được thêm vào tên này.
➤ Đi tới Công thức Tab>> Tên được xác định Nhóm>> Người quản lý tên Tùy chọn.

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

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

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

Sau đó, bạn sẽ được đưa đến Trình quản lý tên Thuật sĩ.
➤ Nhấn Đóng.

3.2:Sử dụng mã VBA để áp dụng danh sách xác thực dữ liệu

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

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

➤ Nhập 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ẽ chỉ thực thi nếu có bất kỳ thay đổi giá trị hoặc bổ sung nào xảy ra và vì vậy chúng tôi đã xác định quy trình là Worksheet_Change , Trang tính là Đối tượng và Thay đổi là Thủ tục .
newitem chứa địa chỉ của ô mà chúng tôi đang thêm các giá trị mới và chúng tôi đã xác định nó là Phạm vi . Loại dữ liệu của phạm vi cập nhật và mục sẽ được coi là Biến thể, nơi chúng tôi đã chỉ định phạm vi cập nhật vào phạm vi có tên được cập nhật của các loại rau và mặt hàng là cho các giá trị của mỗi ô trong phạm vi này.
CHO vòng lặp sẽ chỉ định phạm vi cập nhật cho phạm vi cập nhật và VỚI câu lệnh sẽ tránh sự lặp lại của cùng một đối tượng và cuối cùng, chúng tôi đã thêm xác thực .

Bây giờ, đã đến lúc quay lại trang tính chính và kiểm tra tác dụng sau khi bổ sung sản phẩm Diếp cá .
Như chúng ta có thể thấy, chúng tôi có mặt hàng mới này trong danh sách thả xuống của chúng tôi.

Sau khi chọn mục mới này, chúng tôi sẽ có nó trong ô D6 .

Đọc thêm: Cách sử dụng danh sách xác thực dữ liệu từ một trang tính khác (6 phương pháp)

Phương pháp-4 :Sử dụng Dải ô đã Đặt tên để Tạo Danh sách Thả xuống Có Điều kiện

Tại đây, chúng tôi 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 tôi có hai phạm vi được đặt tên sau, chẳng hạn như fruit1 và rau1 .

Các bước :
➤ Thực hiện theo Bước-01 của Phương pháp-1 .
➤ Viết đoạn 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

NẾU-THEN câu lệnh sẽ kiểm tra xem giá trị trong ô D6 là Trái cây và đối với giá trị này, dưới dạng danh sách, chúng tôi sẽ nhận được phạm vi có tên fruit1 dưới dạng danh sách trong ô E6 nếu không, chúng tôi sẽ nhận được phạm vi được đặt tên là rau1 dưới dạng danh sách trong ô E6 .

➤ Nhấn F5 , sau đó đi tới 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 các loại trái cây cho danh mục là Trái cây trong ô D6 và chọn bất kỳ một mục nào từ danh sách như Quả mâm xôi .

Vì vậy, chúng tôi đã có mặt hàng Quả mâm xôi mong muốn của mình 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 các loại rau sau khi chạy mã.

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

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

Phần thực hành

Để tự mình thực hành, chúng tôi đã cung cấp một Thực hành như bên dưới trong trang tính có tên Thực hành . Hãy làm điều đó một mình.

Kết luận

Trong bài viết này, chúng tôi đã cố gắng đề cập đến các cách sử dụng dải ô đã đặt tên cho danh sách xác thực dữ liệu trong Excel VBA một cách 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 chia sẻ chúng trong phần bình luận.

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

• Cách Sử dụng Xác thực Dữ liệu trong Excel với Màu (4 Cách)
• Excel VBA để tạo danh sách xác thực dữ liệu từ mảng
• Cách Sử dụng Công thức VLOOKUP Tùy chỉnh trong Xác thực Dữ liệu Excel
• [Đã sửa lỗi] Xác thực dữ liệu không hoạt động để sao chép dán trong Excel (với Giải pháp)
• Cách Xóa Khoảng trống khỏi Danh sách Xác thực Dữ liệu trong Excel (5 Phương pháp)