VBA Userform - Transfer multiple listbox items in a multi column listbox to excel?

  • Hi There :)


    I've recently learnt how to create userforms through articles, youtube videos and all the tremendously helpful information in this forum. thank you!


    I have been working on a project to create a basic point of sale system for data capture. I am struggling to work out how to transfer all the listbox items from the first and second column to an individual row each in the excel spreadsheet at the click of one button.


    The code I have is below
    [VBA]Private Sub btnConfirm_Click()


    Dim lRow As Long
    Dim ws As Worksheet
    Dim Item As Object
    Set ws = Worksheets("CafeOrders")
    lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row


    With ws
    .Cells(lRow, 1).Value = Me.TextBoxDate.Value
    .Cells(lRow, 4).Value = Me.txtTotal.Value
    .Cells(lRow, 5).Value = Me.lPaymentMethod.List
    .Cells(lRow, 2),Cells(1row + tSummary.Listcount -1,1)).Value = tSummary.List

    End With

    'Clear input controls
    Me.tSummary.Clear
    txtTotal.Text = Clear
    Me.lPaymentMethod.Clear
    txtCashAmount.Text = Clear


    End Sub[/VBA]


    This is the line that I am not sure of
    [VBA] .Cells(lRow, 2),Cells(1row + tSummary.Listcount -1,1)).Value = tSummary.List[/VBA]


    Originally I had
    [VBA] .Cells(lRow, 2).Value = Me.tSummary.List[/VBA]
    However this was only transferring the first line item from the first column of the list box to the excel cell.


    I would like for each item in the first column of the listbox be to transferred to the next available row in column B of the spreadsheet, and
    for each item in the second column of the listbox be to transferred to the next available row in column C of the spreadsheet, each item in a new row in excel.


    Is this possible? Hope this is not outplace. Apologies if so - this is all very new to me.


    Any help would be very much appreciated.
    Thanks in advance :)

  • One could put the listbox columns in an array and then copy that array to a range, but that would probably be overkill for just two columns. Assuming tSummary has its MultiSelect property set to fmMultiSelectSingle, try something like this:


    Code
    .Cells(lRow, 1).Value = Me.TextBoxDate.Value
    .Cells(lRow, 2).Value = Me.tSummary.List(Me.tSummary.ListIndex,0)
    .Cells(lRow, 3).Value = Me.tSummary.List(Me.tSummary.ListIndex,1)
    .Cells(lRow, 4).Value = Me.txtTotal.Value
    .Cells(lRow, 5).Value = Me.lPaymentMethod.List
  • Thank you [USER="329544"]JonathanVH[/USER] for your quick reply and help. I really appreciate it.


    You're correct in that the listbox tSummary has MultiSelect property set to fmMultiSelectSingle. Will this prevent all listed items from transferring if not selected?


    I've input the above code however I'm getting an error prompt "Run time error '381': Could not get the list property. In proper array index." ...


    If there's anymore detail I can provide please let me know.


    Thanks again. This is new to me, but very interesting and exciting to see it come along.


    Thanks for your help :)

  • You will get that error if there is no row selected in your listbox. If you want the button to work with no selection, you could add an If statement:

    Code
    .Cells(lRow, 1).Value = Me.TextBoxDate.Value
    If Me.tSummary.ListIndex <> -1 Then
      .Cells(lRow, 2).Value = Me.tSummary.List(Me.tSummary.ListIndex,0)
      .Cells(lRow, 3).Value = Me.tSummary.List(Me.tSummary.ListIndex,1)
    End If
    .Cells(lRow, 4).Value = Me.txtTotal.Value.Cells(lRow, 5).Value = Me.lPaymentMethod.List


    If the button should not add a row if there is no Summary chosen, then instead use a similar If statement earlier in the code so it skips creating a row.

    If the listbox did not have its MultiSelect property set to fmMultiSelectSingle, this method would not work; one would instead need to iterate through its rows to find which are selected (and which of those should be placed in your range row?).

  • Thank you again for your help JonathanVH! :smile:


    I would like for all items listed to transfer without the user having to make a selection.


    I have input the above suggested however the cells are appearing blank - no entry being made into the sheet....


    If it is selection that is required, would it be possible for all items to be selected upon clicking the button so that it automatically selects all? Would that be the issue?

  • How are multiple selections to appear in only two cells? Or do you mean that, when no row is selected, each listbox row (or multiple selections if you change the MultiSelect property) is supposed to create a separate row on the worksheet? If that's the case, then, as I wrote, you'll need to iterate through the rows of the listbox and the If statement should have an Else statement to do that when the ListIndex is -1.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!