Adding Multiple Delimited Entries to a List Box

The typical way of entering user-specified data into a list box is one entry at a time; however, you can accept multiple delimited entries at once and add them to a list box with a call to this function. Passing the function a delimited string fills the list box with the values; the list box can be cleared first if requested in the bClear parameter. If you pass an empty string, the values from the list box are used to create a delimited string:

 Function ConvertList(cList As Control, ByVal sText As _	String, ByVal sDelimiter As String, Optional bClear As _	Boolean = False) As String	Dim lLoop As Long	Dim lFind As Long	If Len(sText) Then		If bClear Then cList.Clear		Do			lFind = InStr(sText, sDelimiter)			If lFind Then				cList.AddItem Left$(sText, lFind - 1)				sText = Mid$(sText, lFind + 1)			End If		Loop Until lFind = 0		If Len(sText) Then cList.AddItem sText	Else		For lLoop = 0 To cList.ListCount - 1			If lLoop = cList.ListCount - 1 _				Then sDelimiter = vbNullString			ConvertList = ConvertList & cList.List(lLoop) _				& sDelimiter		Next lLoop	End IfEnd Function

Here’s how you can call it to fill a list, then output the same list using a different delimiter:

 Call ConvertList(List1, "yellow|green|red", "|", True)Debug.Print ConvertList(List1, "", "/")