Multi-select dropdown list in Excel - how to extend VB script to work on additional columns?
Experts,
I'm NOT a VB developer and am trying to extend a code snippet I found online. The following code allows me to convert a standard dropdown menu into one that allows multiple selections. As each additional selection is made using the dropdown menu, the script appends each value separated by a comma.
The code works for column 5 of my spreadsheet and its dropdowns. However, I can't figure out how to alter the code to work for another column - say column 7 - that has dropdowns as well?
I tried copying and pasting the entire piece of code and simply updating the "Worksheet_Change()" class name and changing the reference to column 5 to column 7 but that didn't work.
Any suggestions?
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 StringDim Newvalue As StringApplication.EnableEvents = TrueOn Error GoTo ExitsubIf Target.Column = 5 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 IfEnd IfApplication.EnableEvents = TrueExitsub:Application.EnableEvents = TrueEnd Sub
Simultaneously I also determined that the following code words too:
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 StringDim Newvalue As StringApplication.EnableEvents = TrueOn Error GoTo ExitsubIf Target.Column = 5 Or Target.Column = 7 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 IfEnd IfApplication.EnableEvents = TrueExitsub:Application.EnableEvents = TrueEnd Sub
Simultaneously I also determined that the following code words too:
Open in new window