›09:27:42 05-06-2024 | Lượt xem: 3142
Bài viết hướng dẫn cách tạo menu thả xuống Excel cho phép người dùng chọn nhiều mục có hoặc không trùng lặp.
Excel đã đi được một chặng đường dài kể từ khi ra đời và ngày càng giới thiệu nhiều tính năng hữu ích hơn với mỗi bản phát hành mới. Trong Excel 365, họ đã thêm khả năng tìm kiếm trong danh sách xác thực dữ liệu, giúp tiết kiệm thời gian rất nhiều khi làm việc với tập hợp dữ liệu lớn. Tuy nhiên, ngay cả với tùy chọn mới này, Excel sẵn có vẫn chỉ cho phép chọn một mục từ danh sách tùy chọn được xác định trước. Nhưng đừng sợ, vì đã có giải pháp. Bằng cách sử dụng VBA, bạn có thể tạo danh sách thả xuống với nhiều lựa chọn. Với khả năng ngăn chặn trùng lặp và xóa các mục không chính xác, tính năng này có thể hợp lý hóa việc nhập dữ liệu và cải thiện độ chính xác trong bảng tính Excel của bạn.
Tạo danh sách thả xuống nhiều lựa chọn trong Excel là một quá trình gồm hai phần:
1. Đầu tiên, bạn tạo danh sách xác thực dữ liệu thông thường trong một hoặc nhiều ô.
2. Sau đó, chèn mã VBA vào cuối bảng tính đích.
Nó cũng hoạt động theo thứ tự ngược lại :)
Để chèn danh sách thả xuống trong Excel, bạn sử dụng tính năng Data Validation. Các bước hơi khác nhau tùy thuộc vào việc các mục nguồn nằm trong phạm vi thông thường, phạm vi được đặt tên hay bảng Excel.
Tùy chọn tốt nhất là tạo danh sách xác thực dữ liệu từ một bảng. Vì các bảng Excel có tính chất động nên một danh sách thả xuống có liên quan sẽ tự động mở rộng hoặc thu gọn khi bạn thêm hoặc xóa các mục vào/khỏi bảng.
Trong ví dụ này, chúng ta sẽ sử dụng bảng có tên đơn giản Table1 , nằm trong A2:A25 trong ảnh chụp màn hình bên dưới. Để tạo danh sách chọn từ bảng này, các bước là:
1. Chọn một hoặc nhiều ô cho danh sách thả xuống của bạn (D3:D7 trong trường hợp này).
2. Trên tab Data, trong nhóm Data Tools, bấm vào Data Validation.
3. Trong hộp thả xuống Allow, chọn List.
4. Trong hộp Source, nhập công thức tham chiếu gián tiếp đến cột của Table1 có tên Items.
=INDIRECT("Table1[Items]")
5. Khi hoàn tất, hãy nhấp vào OK.
Để thêm mã VBA vào bảng tính của bạn, hãy làm theo các bước sau:
1. Mở Trình soạn thảo Visual Basic bằng cách nhấn Alt + F11 hoặc nhấp vào tab Developer > Visual Basic. Nếu bạn không có tab này trên ribbon Excel, hãy xem cách thêm tab Developer.
2. Trong ngăn Project Explorer ở bên trái, bấm đúp vào tên trang tính chứa danh sách thả xuống của bạn. Thao tác này sẽ mở cửa sổ Mã cho trang tính đó.
Hoặc bạn có thể nhấp chuột phải vào tab của trang tính và chọn Mã xem từ menu ngữ cảnh. Thao tác này sẽ mở ngay cửa sổ Mã cho một trang nhất định.
3. Trong cửa sổ Mã, dán mã VBA.
4. Đóng VB Editor và lưu tệp của bạn dưới dạng Sổ làm việc hỗ trợ macro (.xlsm).
Khi bạn quay lại bảng tính, danh sách thả xuống sẽ cho phép bạn chọn nhiều mục:
Dưới đây là mã để tạo danh sách xác thực dữ liệu cho phép chọn nhiều mục, bao gồm các lựa chọn lặp lại:
Mã VBA để chọn nhiều mục trong danh sách thả xuống của Excel
Option Explicit
Private Sub Worksheet_Change(ByVal Destination As Range)
Dim DelimiterType As String
Dim rngDropdown As Range
Dim oldValue As String
Dim newValue As String
DelimiterType = ", "
If Destination.Count > 1 Then Exit Sub
On Error Resume Next
Set rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitError
If rngDropdown Is Nothing Then GoTo exitError
If Intersect(Destination, rngDropdown) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newValue = Destination.Value
Application.Undo
oldValue = Destination.Value
Destination.Value = newValue
If oldValue = "" Then
'do nothing
Else
If newValue = "" Then
'do nothing
Else
Destination.Value = oldValue & DelimiterType & newValue
' add new value with delimiter
End If
End If
End If
exitError:
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
Cách mã này hoạt động:
Mã cho phép nhiều lựa chọn trong tất cả các danh sách thả xuống trên một trang cụ thể. Bạn không cần chỉ định tham chiếu ô hoặc phạm vi mục tiêu trong mã.
Mã này dành riêng cho trang tính, vì vậy hãy nhớ thêm mã này vào từng trang tính mà bạn muốn cho phép nhiều lựa chọn trong danh sách thả xuống.
Mã này cho phép lặp lại, tức là chọn cùng một mục nhiều lần.
Các mục đã chọn được phân tách bằng dấu phẩy và dấu cách. Để thay đổi dấu phân cách, hãy thay thế ", " bằng ký tự bạn muốn trong DelimiterType = ", " (dòng 7 trong đoạn mã trên).
Khi chọn từ một danh sách lớn, người dùng đôi khi có thể chọn cùng một mục nhiều lần mà không nhận thấy. Mã bên dưới giải quyết vấn đề trùng lặp trong danh sách thả xuống xác thực dữ liệu có nhiều lựa chọn. Nó cho phép người dùng chọn một mục cụ thể chỉ một lần. Nếu bạn cố gắng chọn lại cùng một mục, sẽ không có gì xảy ra. Khá tuyệt phải không?
Mã VBA để tạo danh sách thả xuống nhiều lựa chọn mà không lặp lại
Option Explicit
Private Sub Worksheet_Change(ByVal Destination As Range)
Dim rngDropdown As Range
Dim oldValue As String
Dim newValue As String
Dim DelimiterType As String
DelimiterType = ", "
If Destination.Count > 1 Then Exit Sub
On Error Resume Next
Set rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitError
If rngDropdown Is Nothing Then GoTo exitError
If Intersect(Destination, rngDropdown) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newValue = Destination.Value
Application.Undo
oldValue = Destination.Value
Destination.Value = newValue
If oldValue <> "" Then
If newValue <> "" Then
If oldValue = newValue Or _
InStr(1, oldValue, DelimiterType & newValue) Or _
InStr(1, oldValue, newValue & Replace(DelimiterType, " ", "")) Then
Destination.Value = oldValue
Else
Destination.Value = oldValue & DelimiterType & newValue
End If
End If
End If
End If
exitError:
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
Khi người dùng cần chọn nhiều tùy chọn nhưng có thể mắc lỗi hoặc thay đổi ý định trong quá trình lựa chọn, danh sách thả xuống nhiều lựa chọn cho phép loại bỏ các mục không chính xác có thể là cứu cánh.
Hãy xem xét một tình huống trong đó bạn cần phân công nhiều nhiệm vụ cho các thành viên trong nhóm bằng danh sách thả xuống. Với chức năng mặc định của Excel, cách duy nhất để loại bỏ nhiệm vụ được giao không chính xác là xóa toàn bộ nội dung của ô và bắt đầu lại. Với khả năng xóa từng mục khỏi vùng chọn, nhóm có thể dễ dàng sửa đổi nhiệm vụ được giao mà không nhầm lẫn hoặc mắc lỗi.
Mã bên dưới triển khai chức năng xóa mục theo cách đơn giản và trực quan: lần nhấp đầu tiên vào một mục sẽ thêm mục đó vào vùng chọn và lần nhấp thứ hai vào cùng mục đó sẽ xóa mục đó khỏi vùng chọn.
Mã VBA để tạo menu thả xuống nhiều lựa chọn cho phép xóa các mục đã chọn
Option Explicit
Private Sub Worksheet_Change(ByVal Destination As Range)
Dim rngDropdown As Range
Dim oldValue As String
Dim newValue As String
Dim DelimiterType As String
DelimiterType = ", "
Dim DelimiterCount As Integer
Dim TargetType As Integer
Dim i As Integer
Dim arr() As String
If Destination.Count > 1 Then Exit Sub
On Error Resume Next
Set rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitError
If rngDropdown Is Nothing Then GoTo exitError
TargetType = 0
TargetType = Destination.Validation.Type
If TargetType = 3 Then ' is validation type is "list"
Application.ScreenUpdating = False
Application.EnableEvents = False
newValue = Destination.Value
Application.Undo
oldValue = Destination.Value
Destination.Value = newValue
If oldValue <> "" Then
If newValue <> "" Then
If oldValue = newValue Or oldValue = newValue & Replace(DelimiterType, " ", "") Or oldValue = newValue & DelimiterType Then ' leave the value if there is only one in the list
oldValue = Replace(oldValue, DelimiterType, "")
oldValue = Replace(oldValue, Replace(DelimiterType, " ", ""), "")
Destination.Value = oldValue
ElseIf InStr(1, oldValue, DelimiterType & newValue) Or InStr(1, oldValue, newValue & DelimiterType) Or InStr(1, oldValue, DelimiterType & newValue & DelimiterType) Then
arr = Split(oldValue, DelimiterType)
If Not IsError(Application.Match(newValue, arr, 0)) = 0 Then
Destination.Value = oldValue & DelimiterType & newValue
Else:
Destination.Value = ""
For i = 0 To UBound(arr)
If arr(i) <> newValue Then
Destination.Value = Destination.Value & arr(i) & DelimiterType
End If
Next i
Destination.Value = Left(Destination.Value, Len(Destination.Value) - Len(DelimiterType))
End If
ElseIf InStr(1, oldValue, newValue & Replace(DelimiterType, " ", "")) Then
oldValue = Replace(oldValue, newValue, "")
Destination.Value = oldValue
Else
Destination.Value = oldValue & DelimiterType & newValue
End If
Destination.Value = Replace(Destination.Value, Replace(DelimiterType, " ", "") & Replace(DelimiterType, " ", ""), Replace(DelimiterType, " ", "")) ' remove extra commas and spaces
Destination.Value = Replace(Destination.Value, DelimiterType & Replace(DelimiterType, " ", ""), Replace(DelimiterType, " ", ""))
If Destination.Value <> "" Then
If Right(Destination.Value, 2) = DelimiterType Then ' remove delimiter at the end
Destination.Value = Left(Destination.Value, Len(Destination.Value) - 2)
End If
End If
If InStr(1, Destination.Value, DelimiterType) = 1 Then ' remove delimiter as first characters
Destination.Value = Replace(Destination.Value, DelimiterType, "", 1, 1)
End If
If InStr(1, Destination.Value, Replace(DelimiterType, " ", "")) = 1 Then
Destination.Value = Replace(Destination.Value, Replace(DelimiterType, " ", ""), "", 1, 1)
End If
DelimiterCount = 0
For i = 1 To Len(Destination.Value)
If InStr(i, Destination.Value, Replace(DelimiterType, " ", "")) Then
DelimiterCount = DelimiterCount + 1
End If
Next i
If DelimiterCount = 1 Then ' remove delimiter if last character
Destination.Value = Replace(Destination.Value, DelimiterType, "")
Destination.Value = Replace(Destination.Value, Replace(DelimiterType, " ", ""), "")
End If
End If
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
exitError:
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
Bản demo bên dưới nêu bật cách hoạt động của danh sách thả xuống nhiều lựa chọn với chức năng loại bỏ trong Excel. Người dùng có thể chọn nhiều tùy chọn từ danh sách xác thực dữ liệu và thực hiện điều chỉnh nhanh chóng. Một cách tiếp cận hợp lý và hiệu quả để quản lý các lựa chọn!
Ký tự phân tách các mục trong vùng chọn được đặt trong tham số DelimiterType. Trong tất cả các mã, giá trị mặc định của tham số này là ", " (dấu phẩy và dấu cách) và nó nằm ở dòng 7. Để sử dụng dấu phân cách khác, bạn có thể thay thế "," bằng ký tự mong muốn. Ví dụ:
Để phân tách các mục đã chọn bằng dấu cách, hãy sử dụng DelimiterType=" ".
Để phân tách bằng dấu chấm phẩy, hãy sử dụng DelimiterType=";" hoặc DelimiterType=";" (có hoặc không có khoảng trắng tương ứng).
Để phân tách bằng thanh dọc, hãy sử dụng DelimiterType=" |".
Ví dụ: nếu bạn thay đổi dấu phân cách thành dấu gạch chéo dọc, danh sách chọn nhiều lựa chọn của bạn sẽ trông như sau:
Để có được mỗi lựa chọn trong một dòng riêng biệt trong cùng một ô, hãy đặt DelimiterType thành Vbcrlf. Trong VBA, nó là một hằng số cho các ký tự xuống dòng và ký tự xuống dòng.
Chính xác hơn, bạn thay đổi dòng mã này:
DelimiterType = ","
đến cái này:
DelimiterType = vbCrLf
Kết quả là mỗi mục bạn chọn từ danh sách thả xuống sẽ xuất hiện trong một dòng mới:
Tất cả các mã được mô tả trong hướng dẫn này đều hoạt động trên toàn bộ trang tính. Tuy nhiên, bạn có thể dễ dàng sửa đổi bất kỳ mã nào để nó chỉ áp dụng cho các ô, hàng hoặc cột cụ thể nếu cần. Đối với điều này, hãy tìm dòng mã này:
If rngDropdown Is Nothing Then GoTo exitError
Thêm ngay sau nó, thêm một dòng mới chỉ định nơi cho phép nhiều lựa chọn, như được giải thích trong các ví dụ bên dưới.
Để cho phép chọn nhiều mục trong một cột nhất định , hãy thêm mã này:
If Not Destination.Column = 4 Then GoTo exitError
Trong đó "4" là số cột mục tiêu. Trong trường hợp này, danh sách thả xuống nhiều lựa chọn sẽ chỉ được bật trong cột D. Trong tất cả các cột khác, danh sách thả xuống sẽ bị giới hạn ở một lựa chọn duy nhất.
Để nhắm mục tiêu một số cột, hãy chỉ định số của chúng bằng mã này:
If Destination.Column <> 4 And Destination.Column <> 6 Then GoTo exitError
Trong trường hợp này, danh sách thả xuống nhiều lựa chọn sẽ có sẵn trong cột D (4) và F (6).
Để chèn nhiều danh sách thả xuống vào một hàng cụ thể, hãy sử dụng mã này:
If Not Destination.Row = 3 Then GoTo exitError
Trong ví dụ này, hãy thay thế "3" bằng số hàng mà bạn muốn bật danh sách thả xuống có nhiều lựa chọn.
Để nhắm mục tiêu nhiều hàng , mã như sau:
If Destination.Row <> 3 And Destination.Row <> 5 Then GoTo exitError
Trong đó "3" và "5" là các hàng được phép chọn nhiều mục.
Để bật nhiều lựa chọn trong các ô cụ thể , hãy thêm một trong các dòng mã bên dưới.
Đối với một ô duy nhất:
If Not Destination.Address = "$D$3" Then GoTo exitError
Đối với nhiều ô:
If Destination.Address <> "$D$3" And Destination.Address <> "$F$6" Then GoTo exitError
Chỉ cần nhớ thay thế "$D$3" và "$F$6" bằng địa chỉ của các ô mục tiêu của bạn.
Để giới hạn danh sách thả xuống nhiều lựa chọn của bạn trong một phạm vi cụ thể, hãy thay thế dòng mã này:
If Intersect(Destination, rngDropdown) Is Nothing Then
với cái này:
If Intersect(ActiveCell, Range("C3:D10")) Is Nothing Or Intersect(Destination, rngDropdown) Is Nothing Then
Phạm vi quan tâm được chỉ định trực tiếp trong mã (C3:D10 trong ví dụ trên). Sửa đổi này cung cấp một cách tiếp cận hiệu quả hơn để bàn giao phạm vi - thay vì liệt kê riêng lẻ 16 ô, bạn sử dụng một tham chiếu phạm vi duy nhất.
Để bật chức năng thả xuống nhiều lựa chọn trong trang tính được bảo vệ, chỉ cần chèn mã sau vào trang tính nơi bạn đã thêm mã chính.
Mã VBA để cho phép nhiều lựa chọn trong trang được bảo vệ
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Unprotect password:="password"
On Error GoTo exitError2
If Target.Validation.Type = 3 Then
Else
ActiveSheet.Protect password:="password"
End If
Done:
Exit Sub
exitError2:
ActiveSheet.Protect password:="password"
End Sub
Trước khi thêm mã này vào trang tính của bạn, hãy nhớ thay "mật khẩu" bằng mật khẩu thực tế bạn đã sử dụng để bảo vệ trang tính. Và đây là thay đổi duy nhất cần được thực hiện. Mã sẽ tự động phát hiện sự hiện diện của danh sách thả xuống trong một ô nhất định và cho phép chỉnh sửa ô đó. Trong tất cả các ô khác, việc chỉnh sửa sẽ vẫn bị hạn chế.
Vậy là bạn đã có nó - một danh sách thả xuống Excel với nhiều lựa chọn. Việc thêm tính năng tuyệt vời này vào bảng tính của bạn sẽ tăng độ chính xác của dữ liệu đầu vào và giúp bạn hoàn thành công việc nhanh hơn!
