Forum Discussion
Multiple Selections in a Drop Down List in Excel - separator
Hi all,
I have a question here I was hoping to get some help with.
I have this VBA code to allow users to select multiple entries from a drop down list
It is working perfectly and furthermore it is working in 2 different columns thanks to these two lines:
Select Case Target.Column
Case 15, 16
Now, the separator for the multiple selections is currently a comma:
Target.Value = Oldvalue & " + " & Newvalue
Is there any way to have a separator for column O (case 15) and another, different separator for column P (case 16)
Full code here:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
Select Case Target.Column
Case 15, 16
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
Case Else:
End Select
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
Claudia350 You can simply repeat (i.e. copy and paste) the code that starts with Case 15, 16 to the row above Case Else:
Then delete the ", 16" in the first Case statement and "15, " in the second one and select the separator of your liking for each of the columns.
6 Replies
- mathetesSilver Contributor
Is there some compelling reason why this has to be accomplished via a VBA macro? With the new Dynamic Array Functions, it's possible to create drop-downs that are themselves dynamic--growing or getting smaller as needed--without needing to resort to macros at all.
If you can post the spreadsheet in question with a more complete description of the nature of the task, maybe somebody here can demonstrate this new functionality. (It is new, to Office 365 subscribers, just in the last year).
- Claudia350Copper Contributor
Hi mathetes
Thanks for the reply!
"Is there some compelling reason why this has to be accomplished via a VBA macro? "
No other reason except it is the only way I know how to do it. 🙂 I'm definitely open to doing it any other way that solves this - and learning something along the way!
Ok, so:
My only real problem is that I'd like to have different separators in columns O and P. See how both columns are multiple selection columns (which is intended - this was achieved with a VBA macro) and the entries are separated by a plus sign on both columns (which is not intended):
That part of the VBA macro is here:
Target.Value = Oldvalue & " + " & Newvalue
I'd like to have one separator for column O and one for column P.
Very simplified file attached in case it helps.
Thanks again!
- Riny_van_EekelenPlatinum Contributor
Claudia350 You can simply repeat (i.e. copy and paste) the code that starts with Case 15, 16 to the row above Case Else:
Then delete the ", 16" in the first Case statement and "15, " in the second one and select the separator of your liking for each of the columns.