how to initialize more than one Listboxes in the same user form

[pimp_mentality]

Hey guys, need some assistance

I have a UserForm form which currently, when opened it displays all data in the workbook (5 columns). This is the code I am using to accomplish this.

Private Sub UserForm_Initialize()
    Dim oneCell As Range, LastRow As Long
    Dim MyArray As Variant
    Dim i As Integer


    ListBox1.ColumnCount = 5
    ListBox1.ColumnWidths = "180;250;180;0;100"
    
    LastRow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
    
    With ListBox1
        For Each oneCell In Sheet1.Range("A2").Resize(LastRow, 1)
            .AddItem
            .List(ListRow, 0) = Cells(oneCell.Row, 1).Value
            .List(ListRow, 1) = Cells(oneCell.Row, 2).Value
            .List(ListRow, 2) = Cells(oneCell.Row, 3).Value
            .List(ListRow, 4) = Cells(oneCell.Row, 5).Value
          
            ListRow = ListRow + 1
        Next oneCell
    End With


End Sub

What I would like to do now is have a second listbox which populates with data from columns, say A and B only from sheet2 when the user form is open.

Any suggestions.


ALSO

the above code is suppose to display 5 columns (A-E, these are the only columns with data) however I notice on initialization column D does not show in the list box. any help in rectifying this issues would also be appreciated

 

[My Aswer Is This]

You showed:
ListBox1.ColumnWidths = "180;250;180;0;100"

Your telling the script to make column four width Zero

<strike></strike>

 

[mikerickson]

To fill the five column list box will all the entries from rows 2 thru lastRow

With Sheet1.Range("A:A")
    Listbox1.List = Range(.Cells(2, 5), .Cells(Rows.Count,1).End(xlup)).Value
End With

 

[My Aswer Is This]

I changed the columns width:

Modify to your liking:

Try this:

Private Sub UserForm_Initialize()
Dim lastrow As Long
Dim lastrowa As Long
ListBox1.ColumnCount = 5
ListBox1.ColumnWidths = "100;100;100;100;100"
lastrow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
Data = Range("A2:E" & lastrow)
ListBox1.List = Data
'Listbox2
ListBox2.ColumnCount = 2
ListBox2.ColumnWidths = "100;100"
lastrowa = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row
Data2 = Sheet2.Range("A2:B" & lastrowa)
ListBox2.List = Data2
End Sub

 

[pimp_mentality]

This works wonderfully, Thanks man however

on of the columns in listbox2 is suppose to display time however it does not it just displays decimal numbers eg. 0.4355633333. Any suggestions to have the actually time displayed in the box?

 

[My Aswer Is This]

I do not know how to do that formatting.

 

[pimp_mentality]

Ok,

Thanks for your help thus far though, Much appreciated

 

[mikerickson]

If the third column is supposed to show time, you could use code like

Dim oneCell as Range
lastrowa = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row

With ListBox2
    .Clear
    For Each oneCell in Sheet2.Range("A2:A" & lastrowa).Rows
        .AddItem oneCell.Value
        .List(.ListCount, 1) = Format(oneCell.Offset(0,1).Value, "hh:mm:ss")
    Next oneCell
End With

 

[My Aswer Is This]

In your original post you said:
What I would like to do now is have a second listbox which populates with data from columns, say A and B only from sheet2 when the user form is open.

Then in post # 5 you said:
on of the columns in listbox2 is suppose to display time however it does not it just displays decimal numbers eg. 0.4355633333.

You did not say what column has time. Is it column A or column B?

<strike></strike>

 

[pimp_mentality]

oh well the time is displayed in column B of sheet 2