Скачать или смотреть Create Multiple Selection Drop List with Copilot

A Data Validation Drop list doesn’t natively allow multiple selections. The only way to do that is to create a VBA code. In my Book “Data Validation a Back Door to Master Excel” (page 210) I explained how to create this code. However with Copilot and AI in Excel I can simply write a prompt to generate this code for me.
In this tutorial I create a basic drop list then I use Copilot in creating a Multiple selection Drop List Functionality in Excel. Although we can prompt Copilot in natural language, however, some basic knowledge of VBA allows you to use that code and fix minor errors if needed.
Let’s dive into Excel

Here is the prompt I wrote:
I have some data validation drop lists in the range C2:C21. I need a VBA code that allows multiple selection from the drop list in any cell in the range C2:C21. When I make another selection, it appears in the cell next to the previous one separated by comma. If I select from the drop list a previously selected item, it removes it from the cell. Please write me the VBA code.

Here is the VBA Code returned by Copilot:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim cell As Range
Dim oldVal As String
Dim newVal As String
Dim valArray() As String
Dim result As String
Dim exists As Boolean
Dim i As Integer

' Define the range with data validation (C1:C7)
Set rngDV = Me.Range("C1:C7")

' Only process if the changed cell is within the range
If Not Intersect(Target, rngDV) Is Nothing Then
Application.EnableEvents = False

For Each cell In Intersect(Target, rngDV)
If cell.Validation.Type = xlValidateList Then
newVal = cell.Value
Application.Undo
oldVal = cell.Value

If oldVal = "" Then
' If the cell was previously empty, just insert the new value
cell.Value = newVal
Else
' Split existing values into array
valArray = Split(oldVal, ", ")
exists = False
result = ""

' Check if new value is already in the list
For i = LBound(valArray) To UBound(valArray)
If valArray(i) = newVal Then
exists = True ' Item already exists
Else
If result = "" Then
result = valArray(i)
Else
result = result & ", " & valArray(i)
End If
End If
Next i

' If item didn't exist, add it
If Not exists Then
If result = "" Then
cell.Value = newVal
Else
cell.Value = result & ", " & newVal
End If
Else
cell.Value = result
End If
End If
End If
Next cell

Application.EnableEvents = True
End If
End Sub

We deliver Corporate Training in Canada and across the the globe. For our course Catalog or booking, visit:
www.OfficeInstructor.com