Adding ", " to concatenated text

  • Thread starter Thread starter Pamela
  • Start date Start date
P

Pamela

I have a control that is to display in the On Enter event, a concatenated
sentence including the users choices in a multi-select list box. The code is
mainly from nice people here which I've taken bits from and added to and it
almost works except for getting a comma and space between the entries. Here
is the result I'm getting:
"There is damage to A/C systemCooling systemDecklid/tailgate,."
I want it to be:
"There is damage to the A/C system, Cooling system, Decklid/tailgate."
Here is my code:
Dim varRow As Variant, strText As String
strText = vbNullString
For Each varRow In Me.lbDamagedParts.ItemsSelected
strText = strText & Me.lbDamagedParts.Column(0, varRow)
Me.Text39 = "There is damage to " & strText & ", "
Next varRow
Me.Text39 = Left(Me.Text39, Len(Me.Text39) - 1) & "."

Thank you so much for helping me get through this!!

Pamela
 
I have a control that is to display in the On Enter event, a concatenated
sentence including the users choices in a multi-select list box. The code is
mainly from nice people here which I've taken bits from and added to and it
almost works except for getting a comma and space between the entries. Here
is the result I'm getting:
"There is damage to A/C systemCooling systemDecklid/tailgate,."
I want it to be:
"There is damage to the A/C system, Cooling system, Decklid/tailgate."
Here is my code:
Dim varRow As Variant, strText As String
strText = vbNullString
For Each varRow In Me.lbDamagedParts.ItemsSelected
strText = strText & Me.lbDamagedParts.Column(0, varRow)
Me.Text39 = "There is damage to " & strText & ", "
Next varRow
Me.Text39 = Left(Me.Text39, Len(Me.Text39) - 1) & "."

Thank you so much for helping me get through this!!

Pamela
Click to expand...

You're almost there! Append a comma and a blank for each value you append, and
then trim off the last comma at the end:

Dim varRow As Variant, strText As String
strText = vbNullString
For Each varRow In Me.lbDamagedParts.ItemsSelected
strText = strText & Me.lbDamagedParts.Column(0, varRow) & ", "
Me.Text39 = "There is damage to " & strText & ", "
Next varRow
Me.Text39 = Left(Me.Text39, Len(Me.Text39) - 2) & "."
 
Back
Top