Danh sách thả xuống xác thực dữ liệu là một tính năng rất hữu ích để thực hiện các tác vụ khác nhau liên quan đến Excel. Triển khai VBA là phương pháp hiệu quả nhất, nhanh nhất và an toàn nhất để chạy bất kỳ thao tác nào trong Excel. Trong bài viết này, chúng tôi sẽ chỉ cho bạn 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 xuống sổ làm việc Excel thực hành miễn phí từ đây.
7 phương pháp với VBA trong danh sách thả xuống xác thực dữ liệu trong Excel
Trong phần này, bạn tìm hiể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 VBA macro.
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 với VBA , hãy làm theo các bước bên dưới.
Các bước:
- Khi bắt đầu, nhấn Alt + F11 trên bàn phím của bạn hoặc chuyển đến tab Nhà phát triển -> Visual Basic để mở Visual Basic Editor .
- Sau đó, trong cửa sổ mã bật lên, từ thanh trình đơn, nhấp vào Chèn -> Mô-đun .
- Sau đó, sao chép mã sau và dán nó vào cửa sổ mã.
Sub CreateDropDownList()
Range("B5").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="Grapes, Orange, Guava, Mango, Apple"
End SubMã của bạn hiện đã sẵn sàng để chạy.
Đoạn mã này sẽ tạo danh sách thả xuống trong Ô B5 . Và danh sách thả xuống sẽ mang các giá trị “ Nho, Cam, Ổi, Xoài, Táo “.
- Bây giờ, nhấn F5 trên bàn phím của bạn hoặc từ thanh trình đơn, chọn Run -> Run Sub / UserForm . Bạn cũng có thể chỉ cần nhấp vào biểu tượng Run nhỏ trong thanh menu phụ để chạy macro.
Sau khi thực thi mã, hãy nhìn vào hình ảnh bên dưới để xem kết quả.
Như chúng ta có thể thấy từ hình ảnh trên là có một danh sách thả xuống được tạo trong Ô B5 với các giá trị “ Nho, Cam, Ổi, Xoài, Táo “.
Đọc thêm: Cách tạo danh sách thả xuống trong Excel để xác thực dữ liệu (8 cách)
2. Tạo danh sách thả xuống theo phạm vi được đặt tên bằng VBA trong Excel
Khi bạn không muốn viết từng giá trị của danh sách thả xuống bên trong mã, thì bạn có thể đặt tất cả các tên bên trong một tên đã xác định và sau đó sử dụng nó để gọi phạm vi giá trị. Đây là một phương pháp rất thuận tiện để tạo danh sách thả xuống trong Excel.
Trong phần này, bạn sẽ tìm hiểu cách tạo danh sách thả xuống từ một danh sách nhất định bằng cách sử dụng Phạm vi được đặt tên với VBA mã.
Các bước:
- Trước hết, hãy chọn phạm vi nơi có các giá trị của danh sách thả xuống (trong trường hợp của chúng tôi, phạm vi là B5:B9 ).
- Thứ hai, nhấp chuột phải trên phạm vi đã chọn.
- Một danh sách các tùy chọn sẽ xuất hiện. Từ đó, chọn Xác định tên…
- Sau đó, một Tên mới hộp bật lên sẽ xuất hiện. Trong Tên , viết tên bất kỳ mà bạn thích (chúng tôi đã đặt tên cho ô của mình là Trái cây ).
- Sau đó, nhấp vào OK .
- Chúng tôi đã đặt tên thành công cho dải ô B5:B9 Trái cây (hiển thị trong hình dưới đây).
Bây giờ chúng tôi sẽ sử dụng tên được xác định này trong mã VBA của chúng tôi . Các bước để làm điều đó được hiển thị bên dưới.
- Tương tự như trước đây, mở Visual Basic Editor từ Nhà phát triển và Chèn a Mô-đun trong cửa sổ mã.
- Sau đó, trong cửa sổ mã, sao chép mã sau và dán nó.
Sub GenerateDropDownList()
Range("B12").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=Fruits"
End SubMã của bạn hiện đã sẵn sàng để chạy.
Đoạn mã này sẽ tạo danh sách thả xuống trong Ô B12 với các giá trị “ Nho, Cam, Ổi, Xoài, Táo ”Được định nghĩa trong tên Trái cây .
- Sau đó, Chạy macro. Kết quả được hiển thị trong hình ảnh bên dưới.
Kết quả là, chúng ta có thể thấy từ hình ảnh trên là có một danh sách thả xuống được tạo trong Ô 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 nhiều lựa chọn trong Excel
3. Tạo Hộp thả xuống Xác thực Dữ liệu từ Danh sách Đã cho với Macro
Nếu bạn không thích tùy chọn Phạm vi được đặt tên, thì phần này là dành cho bạn. Tại đây, bạn sẽ học cách tạo danh sách thả xuống từ một phạm vi tồn tại trong một tập dữ liệu.
Các bước:
- Như được hiển thị trước đây, hãy mở Visual Basic Editor từ Nhà phát triển và Chèn a Mô-đun trong cửa sổ mã.
- Sau đó, sao chép mã sau và dán nó vào cửa sổ mã.
Sub ProduceDropDownList()
With Range("B12").Validation
.Add xlValidateList, xlValidAlertStop, xlBetween, "=$B$5:$B$10"
.InCellDropdown = True
End With
End SubMã của bạn hiện đã sẵn sàng để chạy.
Đoạn mã này sẽ tạo ra một danh sách thả xuống trong Ô B12 với giá trị hiện diện trong phạm vi B5:B9 .
- Bây giờ, Chạy macro và nhìn vào hình ảnh sau để xem kết quả.
Kết quả là, chúng ta có thể thấy từ hình ảnh trên là có một danh sách thả xuống được tạo trong Ô B12 với các giá trị “ Nho, Cam, Ổi, Xoài, Táo ”Mà chúng tôi đã lưu trữ trong Ô B5 đến B9 trong trang tính.
Nội dung có Liên quan: Giá trị mặc định trong danh sách xác thực dữ liệu 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)
- Chỉ xác thực dữ liệu bằng chữ và số (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 Sử 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 vĩ mô. Hãy xem cách thực hiện điều đó trong Excel.
Các bước:
- Đầu tiên, hãy mở Visual Basic Editor từ Nhà phát triển và Chèn a Mô-đun trong cửa sổ mã.
- Thứ hai, trong cửa sổ mã, sao chép mã sau và dán nó.
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 SubMã của bạn hiện đã sẵn sàng để chạy.
Mã này sẽ tạo danh sách thả xuống trong mọi ô từ phạm vi B5 đến B9 .
- Thứ ba, Chạy macro. Hãy xem ảnh gif bên dưới để biết kết quả.
Từng và mọi ô từ phạm vi B5 đến B9 hiện đang giữ một 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 chức năng do người dùng xác định
Bạn cũng có thể tạo danh sách thả xuống với chức năng do người dùng xác định (UDF) trong Excel.
Các bước để có được điều đó được hiển thị bên dưới.
Các bước:
- Ở phần đầu, nhấp 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.
- Sau đó, chọn Xem mã từ danh sách đã xuất hiện. Như được hiển thị bên dưới, chúng tôi nhấp chuột phải trên trang tính có tên UDF nơi tập dữ liệu của chúng tôi được lưu trữ và chọn Mã chế độ xem từ các tùy chọn.
- Sau đó, sao chép mã sau và dán nó vào cửa sổ mã được tạo tự động.
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 nó.
- Sau đó, quay lại trang tính quan tâm.
- Sau đó, chọn bất kỳ ô nào nơi bạn muốn tạo danh sách thả xuống (trong trường hợp của chúng tôi, đó là Ô B11 ).
- Trong ô đó, hãy viết hàm mới được tạo - DropDownUDF - cách bạn viết bất kỳ hàm nào khác. Có nghĩa là, trước tiên hãy đặt một dấu bằng (=) , sau đó viết tên hàm, DropDownUDF và chuyển các tham chiếu ô ( B5:B9 ) bên trong dấu ngoặc đơn.
Công thức trong Ô B11 sẽ như thế này:
=DropDownUDF(B5:B9)
- Sau đó, nhấn Enter .
Kết quả là bạn sẽ nhận được danh sách thả xuống được tạo bởi UDF trong Ô B11 với các giá trị “ Nho, Cam, Ổi, Xoài, Táo ”Được lưu trữ trong phạm vi B5:B9 và chúng tôi đã chuyển vào bên trong hàm.
Đọc thêm: 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)
6. Trích xuất dữ liệu từ các trang tính khác nhau trong danh sách thả xuống với VBA
Nhìn vào hình ảnh sau đây. Chúng tôi có một tập dữ liệu trong trang tính có tên Danh sách .
Những gì chúng ta sẽ làm ở đây là, chúng ta sẽ tạo danh sách thả xuống trong Ô B5 của trang tính có tên Mục tiêu (hiển thị trong hình bên dưới). And the values of that dropdown list will be the values from the range B5:B9 of the List trang tính.
Let’s see the steps on how to do that with VBA .
Các bước:
- Firstly, open Visual Basic Editor từ Nhà phát triển và Chèn a Mô-đun trong cửa sổ mã.
- Then, copy the following code and paste it into the code window.
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 SubMã của bạn hiện đã sẵn sàng để chạy.
- Later, Run the macro and look at the following image to see the output.
As a result of the successful code execution, there is a dropdown list created in Cell B5 of the Target worksheet with the values “Grapes, Orange, Guava, Mango, Apple ” that are stored in range B5:B9 of the List spreadsheet.
Đọ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)
7. Deleting Data Validation Drop Down List from Excel with VBA Macro
This section will show you how to delete a dropdown list from Excel. We will show you how to delete the existing dropdown list in Cell B5 ( shown in the image below) with the VBA macro.
The steps to execute that are given below.
Các bước:
- At first, open Visual Basic Editor từ Nhà phát triển và Chèn a Mô-đun trong cửa sổ mã.
- Then, in the code window, copy the following code and paste
Sub DeleteDropDownList()
Range("B5").Validation.Delete
End SubMã của bạn hiện đã sẵn sàng để chạy.
- Later, Run the macro and look at the following image.
As you can see from the above image is that there is no dropdown list anymore in Cell B5 . Finally, we have learnt how to delete an existing dropdown list from a spreadsheet with VBA .
Đọc thêm: 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)
Kết luận
To conclude, this article showed you 7 different applications of the data validation drop-down list in Excel with the VBA macro . Tôi hy vọng bài viết này rất hữu ích cho bạn. Vui lòng đặt bất kỳ câu hỏi nào liên quan đến chủ đề này.
Các bài viết có liên quan
- [Đã 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 sử dụng Dải ô đã Đặt tên cho Danh sách Xác thực Dữ liệu với VBA trong Excel
- Excel VBA để tạo danh sách xác thực dữ liệu từ mảng
- Cách Sử dụng Xác thực Dữ liệu trong Excel với Màu (4 Cách)
- Áp dụng Nhiều Xác thực Dữ liệu trong Một Ô trong Excel (3 Ví dụ)