none
Excel 2013 VBA formatting Listbox 2 columns RRS feed

  • Question

  • Have 2 listbox - Listbox 1 has 9 columns and is populated by Excel Worksheet Range. Listbox 1 shows formats correctly.

    User selects multiple items from Listbox1 and click a command button to inserts into Listbox2. In Listbox2 the 5th column should show data as h:mm but does not it looks like text. Also, in Listbox2 column 9 should be formatted as date mm/dd/yy and it is not.

    How do I write the code to format these columns?

    Code for the Command button is below:

    Private Sub CmdInsert_Click()
        Dim IndexRow As Long
        Dim IndexCol As Long
                 
         With ListBox1
             For IndexRow = 0 To .ListCount - 1
                 If .Selected(IndexRow) Then
                     ListBox2.AddItem .List(IndexRow, 0)
                     For IndexCol = 1 To .ColumnCount - 1
                         ListBox2.List(ListBox2.ListCount - 1, IndexCol) = .List(IndexRow, IndexCol)
                      
                         Next
                  End If
             Next
         End With
      
    Me.ListBox1.MultiSelect = fmMultiSelectSingle
     Me.ListBox1.ListIndex = -1
     Me.ListBox1.MultiSelect = fmMultiSelectMulti
      

    Tuesday, March 24, 2015 12:45 AM

All replies

  • A listbox displays the text of the cells in its rowsource. But rowsource can only be a block of cells, so your code needs to read the text from the source cells of listbox1.

        With ListBox1
            For IndexRow = 0 To .ListCount - 1
                If .Selected(IndexRow) Then
                    ListBox2.AddItem .List(IndexRow, 0)
                    For IndexCol = 1 To .ColumnCount - 1
                        ListBox2.List(ListBox2.ListCount - 1, IndexCol) = _
                        Range(ListBox1.RowSource).Cells(IndexRow + 1, IndexCol + 1).Text
                    Next
                End If
            Next
        End With
        

    Tuesday, March 24, 2015 12:44 PM
  • It worked! I can't Thank You enough for the help. Been working on that problem for days.

    a

    Tuesday, March 24, 2015 10:31 PM