EXCEL - VBA: writte a column of dates in a ListBox and paste them in a column of a worksheet RRS feed

  • Question

  • Dear friends,
    I would like to ask you if it is possible in VBA for Excel, to write a column of data "by hand" into a ListBox, and after to accept it with a command button, and send these dates in a column of an excel worksheet.
    In other words: It is very easy to write 1 line in a Msg Box (text or values), after click on a "command button" and paste this data or text, for example, in cell A1.
    Is it possible to do the same with a ListBox for example with a column of dates?

    Thank you,

    Sunday, February 5, 2017 9:42 PM

All replies

  • Yes - set your listbox to multiselect, and use code like this:

    This code will populate the listbox with the values from A1:A10 of the activesheet, and enter the selected values into column B.

    Private Sub UserForm_Initialize()
        Me.ListBox1.List = Range("A1:A10").Value
        Me.ListBox1.Selected(0) = False
    End Sub

    Private Sub CommandButton1_Click()
        With Me.ListBox1
           For i = 0 To .ListCount - 1
              If .Selected(i) = True Then
                 Cells(Rows.Count, "B").End(xlUp)(2).Value = .List(i)
              End If
           Next i
        End With
    End Sub

    Monday, February 6, 2017 4:24 PM