Are commas possible in a List Box?

[williamlove]

When I use the VBA AddItem method to add an entry to a List Box, I get line breaks when the string contains a comma. Here is an example for the case of the some music artists, the contents of the list box appear like this:

Jackson Browne
The Beatles
Crosby
Stills
Nash and Young

instead of this:

Jackson Browne
The Beatles
Crosby, Stills, Nash and Young

This is a real inconvenience and I hope it is not an unavoidable limitation. Thanks.

 

[georgedwilkinson]

Can you show us the exact VBA code that seems to cause this along with the input data?

It looks like your input data is a comma separated list. If that is true, it is doing exactly what VBA should do to a comma separated list.

 

[williamlove]

I have a List Box called "lstSongList" and the line of code that populates it is near the end of this procedure, right above the Next statement. The line produces a list box with entries like this

1) I Alone -- Live
2) Suspicions -- Eddie Rabbit
3) Wooden Ships -- Crosby
Stills
Nash and Young
4) Prime Time -- The Tubes


Private Sub ScatterFields(objCD As CD)
' Copy values from a CD object out to the form's controls.
Dim iTrackCount As Integer
Dim iTrackIndex As Integer
Dim sFullDigits As String
If objCD.Load() Then
Call ClearListBox(Me.lstSongList)
With objCD
Me!cboCDID.Value = .ID ' This is redundant when this call was a result of combo box, but is not redundant for all other cases
Me!txtCDMusicCategory = .CDMusicCategory
iTrackCount = objCD.Songs.Count
For iTrackIndex = 1 To iTrackCount
'Debug.Print objCD.Songs.Item(iTrackIndex).Name
If iTrackIndex < 10 Then
sFullDigits = "0" & iTrackIndex
Else
sFullDigits = iTrackIndex
End If
lstSongList.AddItem (sFullDigits & ") " & objCD.Songs.Item(iTrackIndex).Name & " -- " & objCD.Songs.Item(iTrackIndex).Artist)

Next
End With
End If
End Sub

 

[DCrake]

Simple Software Solutions

Store the following line to a variable and Debug.Print to view it, then ask your self the following:

(sFullDigits & ") " & objCD.Songs.Item(iTrackIndex).Name & " -- " & objCD.Songs.Item(iTrackIndex).Artist)

Do it look right?

What were you expecting?

Are they the same?

CodeMaster:

 

[williamlove]

CodeMaster, I followed your suggestion by adding this line in front of my AddItem

Debug.Print sFullDigits & ") " & objCD.Songs.Item(iTrackIndex).Name & " -- " & objCD.Songs.Item(iTrackIndex).Artist

The following appears in the Immediate Window for one of the CDS. Item 10 does not get extra lines...but it does when the item is added to my list box. I'm stumped.

01) Woman -- Brother Cane
02) Wheels Of Fortune -- The Doobie Brothers
03) Beginnings -- Chicago
04) Grey Seal -- Elton John
05) Your Gold Teeth II -- Steely Dan
06) Toulouse Street -- The Doobie Brothers
07) Lines On My Face -- Peter Frampton
08) Freaks -- Live
09) Lakini's Juice -- Live
10) Ohio -- Crosby, Stills, Nash and Young ................I cannot get this into my list box as it appears here...a new line is created at each comma.
11) A Stone's Throw Away -- Brother Cane
12) Too Rolling Stoned -- Robin Trower
13) Bridge of Sighs -- Robin Trower
14) Sweet Wine Of Love -- Robin Trower
15) Juke Box Hero -- Foreigner
16) You've Made Me So Very Happy -- Blood, Sweat & Tears................same thing...okay in intermed window but a new line is created at the comma in list box.

 

[georgedwilkinson]

I don't see where you assigned the string to a variable. Next step is to call AddItem with the variable.

I also noticed that in the call to AddItem, you are calling it with () around the argument. Have you tried calling it without the ()?

What I'm thinking is that AddItem is seeing the "," as a delimiter, somewhat like it's documented use of ";" as a delimiter. By using a variable instead of a string, it might behave better.

 

[williamlove]

George, I followed your suggestion, here is the modified code:

Private Sub ScatterFields(objCD As CD)
' Copy values from a CD object out to the form's controls.
Dim iTrackCount As Integer
Dim iTrackIndex As Integer
Dim sFullDigits As String
If objCD.Load() Then
Call ClearListBox(Me.lstSongList)
With objCD
Me!cboCDID.Value = .ID ' This is redundant when this call was a result of combo box, but is not redundant for all other cases
Me!txtCDMusicCategory = .CDMusicCategory
iTrackCount = objCD.Songs.Count
For iTrackIndex = 1 To iTrackCount
If iTrackIndex < 10 Then
sFullDigits = "0" & iTrackIndex
Else
sFullDigits = iTrackIndex
End If
Dim sFullName As String
sFullName = sFullDigits & ") " & objCD.Songs.Item(iTrackIndex).Name & " -- " & objCD.Songs.Item(iTrackIndex).Artist
Debug.Print sFullName
lstSongList.AddItem sFullName

Next
End With
End If
End Sub


The result is still the same, the comma is causing the line breaks. But in the immediate window the entry is correct. I am still stumped.

 

[georgedwilkinson]

Just for fun, replace the "," with something else and see what it does.

sFullName = Replace(sFullName,",","|")

If you still see commas in the immediate window after this, you have non-comma commas (aka mutant commas).

If it does work, try replacing "," with it's Ascii equivalent (use chr(44)). I'll frankly be amazed if it does anything different but wierder things have happened.

If that does work, my guess is that the commas in your string are mutant commas.

 

[williamlove]

George I fixed the problem by doing the code below; I don't fully understand why I had to do that but it works and I'm grateful. Thanks for your time. If you know why this works tell me....

Dim sFullName As String
sFullName = sFullDigits & ") " & objCD.Songs.Item(iTrackIndex).Name & " -- " & objCD.Songs.Item(iTrackIndex).Artist
lstSongList.AddItem """" & sFullName & """"

 

[georgedwilkinson]

It forces the argument to AddItem to be a single string with a " at the beginning and a " at the end.