VBA Code to allow Multiple Selections in a Drop-down List - NEED TO APPLY TO ENTIRE COLUMN 'C'

Question

i have submitted a VBA Code to allow Multiple Selections in a Drop-down List as shown below. However, this code specifies for only cell 'C6' as shown in the 8th line (If Target.Address = "$C$6" Then). I NEED this multiple selections in a Drop-down list to be applied to the entire columns 'C', 'L', 'M'. Thank you

Private Sub Worksheet_Change(ByVal Target As Range)

'Code by Sumit Bansal from https://trumpexcel.com

' To allow multiple selections in a Drop Down List in Excel (without repetition)

Dim Oldvalue As String

Dim Newvalue As String

Application.EnableEvents = True

On Error GoTo Exitsub

If Target.Address = "$C$6" Then

If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then

GoTo Exitsub

Else: If Target.Value = "" Then GoTo Exitsub Else

Application.EnableEvents = False

Newvalue = Target.Value

Application.Undo

Oldvalue = Target.Value

If Oldvalue = "" Then

Target.Value = Newvalue

Else

If InStr(1, Oldvalue, Newvalue) = 0 Then

Target.Value = Oldvalue & ", " & Newvalue

Else:

Target.Value = Oldvalue

End If

End If

End If

End If

Application.EnableEvents = True Exitsub: Application.EnableEvents = True

End Sub

Answer 1

Like this:

Private Sub Worksheet_Change(ByVal Target As Range) 'Code by Sumit Bansal from https://trumpexcel.com ' To allow multiple selections in a Drop Down List in Excel (without repetition) Dim OldValue As String Dim NewValue As String If Target.CountLarge > 1 Then Exit Sub If Intersect(Range("C:C,L:M"), Target) Is Nothing Then Exit Sub On Error Resume Next If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then Exit Sub If Target.Value = "" Then Exit Sub Application.EnableEvents = False On Error GoTo ExitSub NewValue = Target.Value Application.Undo OldValue = Target.Value If OldValue = "" Then Target.Value = NewValue ElseIf InStr(1, OldValue, NewValue) = 0 Then Target.Value = OldValue & ", " & NewValue Else Target.Value = OldValue End If ExitSub: Application.EnableEvents = True End Sub

Answer 2

Thank you so much, @HansV MVP. This VBA Code is working. However, now I am getting an error notification reading "The value in this cell is invalid or missing. Click on 'Display Type Information' for specific details." The 'Display Type Information' reads:

Data Type: List
Restriction: Value must match one of the listed items.
Blanks will be ignored and are automatically valid.

Any suggestions? Thank you!

Answer 3

Could you create a stripped-down copy of the workbook demonstrating the problem (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.

Answer 4

Please see link to document below. You need to download the document in order to select multiple options on column 'C' as an example. Once you select the multiple option in the drop-down cell, the VBA code works and you can see the multiple options, though the error notification pops up. Trying to get rid of that / understand why this is happening. Thank you!

https://docs.google.com/spreadsheets/d/1FQnJSt6in11RWuHia4O4wRor7rmaxe7-/edit?usp=sharing&ouid=114846657996748149136&rtpof=true&sd=true

Answer 5

Thanks. It works correctly for me. I can select multiple items without getting an error message:

You have to be aware of a limitation: after selecting more than one item, you cannot edit the cell contents the normal way anymore. Direct editing only allows single values from the drop-down list. If you want another selection, clear the cell and start anew.

(You might clear the check box 'Show error alert after invalid data is entered' in the Error Alert tab of the Data Validation dialog, but then the user would be able to enter anything in the cells)