VBA: Userform Listbox to String

Question

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?

"seems not to work" - always useful to explain what it does instead of work. — Tim Williams
– Tim Williams, Commented Apr 6, 2022 at 16:19

FaneDuru · Accepted Answer · 2022-04-06 16:20:53Z

1

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...

answered Apr 6, 2022 at 16:20

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA: Userform Listbox to String

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related