How to get a "," between selections in VBA code for Multiple selections from a drop down list

[JohnBell79]

Hi,

I have written this code, with a little help from this forum. I am now looking to add in a "," between selections so that the cell does not automatically try to wrap the text.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim old_val As String
Dim new_val As String
Application.EnableEvents = True
On Error GoTo Exitsub
If (Target.Column = 3) And (Target.Row > 1) Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
new_val = Target.Value
Application.Undo
old_val = Target.Value
If old_val = "" Then
Target.Value = new_val
Else
If InStr(1, old_val, new_val) = 0 Then
Target.Value = old_val & vbNewLine & new_val
Else:
Target.Value = old_val
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

So hopefully finished cell would look like this

Instead of this

Thanks you.

 

[Bosquedeguate]

What if you just use the Replace() function in your VBA code. Your code would look something like

if rng = a range variable pointing to your cell

rng = Replace(rng, vbNewLIne,",") 'replaces all occurence of a new line characeter with a comma (",")

 

[JohnBell79]

Hmm, That didn't seem to work for me when I tried using the code as it was written.

 

[Bosquedeguate]

Code Test

Cell Before macro

Apples Oranges Bananas

Cell After macro

Apples,Oranges,Bananas

VBA macro - note the line feed character in my example was a vbLf character (ASCII value 10)

Sub NewLineToComma(Target As Variant)
  Dim rng As Range
  Set rng = Target
  rng = Replace(rng, vbLf, ",")
End Sub

Tested from the VB "immediate window" with the following line
NewLineToComma(Worksheets("Sheet1").Cells(108,1))

 

[Bosquedeguate]

BTW
vbLf = 10
vbCr = 13
vbCrLr - 13,10 (two characters)
vbNewLine 13, 10 (two characters)

 

[JohnBell79]

Thank you! Not had a chance to try it yet but I will do and let you know! Thanks again

 

[Bosquedeguate]

You should do a test to make sure the character(s) between words are what you are replacing.

I use the VB "Immediate Window" to do these tests. I set a breakpoint in my code then to test the characters separating words uning my example of "Apples Oranges Bananas"

?Asc(Mid(Target,7,1)) & " " & Asc(Mid(Target,8,1)) 'displays the numeric value of the 7th and 8th characters. This is how I determined that there was a vbLf character separating words.