VBA: Userform Listbox to String-CodePudding

I have a Userform where people loop through and add on each click an item to a listbox. By clicking another buttom the items should be converted to a string with a comma as delimiter.

lstDatabase.AddItem txtfruit.Value

I tried to convert the listbox to an array and then to a string but this seems not to work

Dim deliverynoteArray() As Variant
Dim deliverynote_list As String

deliverynoteArray = lstDatabase.List
deliverynote_list = Join(deliverynoteArray, ",")

The "add" and the "convert" are both in Private Sub _click sections. Might that be the problem? That the data is not accessible?

CodePudding user response：

When use deliverynoteArray = lstDatabase.List the code will create a 2D array, and Join works only on 1D arrays. So, after placing the list box element in the array you should use Transpose, which transforms a 2D array having multiple rows and one column, in a 1D type.

Try replacing deliverynote_list = Join(deliverynoteArray, ",") with:

   deliverynote_list = Join(Application.Transpose(deliverynoteArray), ",")
   Debug.Print deliverynote_list 'just to see it in Immediate Window...