Forum Discussion

Claudia350's avatar
Claudia350
Copper Contributor
Apr 29, 2020
Solved

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

  • mathetes's avatar
    mathetes
    Silver Contributor

    Claudia350 

     

    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).

    • Claudia350's avatar
      Claudia350
      Copper 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_Eekelen's avatar
        Riny_van_Eekelen
        Platinum 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. 

Resources