Forum Discussion
Multiple Selections in a Drop Down List in Excel - separator
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.
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).
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!
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.
Claudia350 You're welcome!
I misunderstood what you were looking to accomplish. It's entirely possible that what you're doing DOES require a macro/VBA routine. I was picturing something different after reading your description. So perhaps somebody else can help you. Best wishes.