How to populate listbox from table

[DB73]

Hi peeps,

im trying to get some data into a userform listbox
i've got a table "dumpstats" on sheet "dump stats" with columns A:BF, with headers
and i'm trying to populate the userform listbox1 with some columns from that table.
i can populatie it with all the columns but i need only a few of them like colomn; B, D, BF etc....or with the headernames
Also it can be either column C,A,D...so, not in alphabetic order.

so actualy the question....how to populate the listbox with columns i want to see.

thanks in advance

 

[DB73]

Sorry, you did say that you had a Table. In that case, since the header row is not included in the range defined by its name, try the following code instead...

    Dim rng As Range
    With ThisWorkbook.Worksheets("dump stats").Range("dumpstats")
        Set rng = .Offset(-1, 0).Resize(.Rows.Count + 1)
    End With
   
    Dim filt As Variant
    filt = Application.Filter(rng, Evaluate("ISNUMBER(MATCH(INDEX(" & rng.Rows(1).Address(external:=True) & ",1,0),{""Header2"",""Header4""},0))"))
   
    If Not IsError(filt) Then
        Me.ListBox1.List = filt
    End If

Hope this helps!

still no list

this is what i did

Private Sub UserForm_Initialize()
    Dim rng As Range
    With ThisWorkbook.Worksheets("dump stats").Range("dumpstats")
        Set rng = .Offset(-1, 0).Resize(.Rows.Count + 1)
    End With
    
    Dim filt As Variant
    filt = Application.Filter(rng, Evaluate("ISNUMBER(MATCH(INDEX(" & rng.Rows(1).Address(external:=True) & ",1,0),{""Datum"",""jaar""},0))"))
    
    If Not IsError(filt) Then
        Me.ListBox1.List = filt
    End If
End Sub

 

[DB73]

Use data valiation list without VBA code!

View attachment 79636

Phh

thanks for the help, but this is not what i mean....

 

[Domenic]

With the exact same code in the initialize event handler, here are a couple of examples for you. For columns {""Datum"",""jaar""} . . .

For columns {""Header1"",""Header3"",""Header4""} . . .

Does this help? If not, try post a small sample of your data, preferably using the xl2BB add-in.

 

[DB73]

With the exact same code in the initialize event handler, here are a couple of examples for you. For columns {""Datum"",""jaar""} . . .

View attachment 79777

For columns {""Header1"",""Header3"",""Header4""} . . .

View attachment 79779

Does this help? If not, try post a small sample of your data, preferably using the xl2BB add-in.

got it working now with:

Private Sub UserForm_Initialize()
 
With ListBox1
    .ColumnCount = 9
    .ColumnWidths = "50;60;40;175;60;80;70;70;70"

End With

LstRow = Cells(Rows.Count, 1).End(xlUp).Row
For a = 0 To LstRow - 2
    B = a + 2
    ListBox1.AddItem
    ListBox1.List(a, 0) = Cells(B, 2)                                    '"jaar"                listbox1.List(a, kolomnr.van listbox) = Cells(B, kolomnr.van tabel)
    ListBox1.List(a, 1) = Cells(B, 8)                                    '"periode"             listbox1.List(a, kolomnr.van listbox) = Cells(B, kolomnr.van tabel)
    ListBox1.List(a, 2) = Cells(B, 10)                                   '"week"                listbox1.List(a, kolomnr.van listbox) = Cells(B, kolomnr.van tabel)
    ListBox1.List(a, 3) = Cells(B, 14)                                   '"project"             listbox1.List(a, kolomnr.van listbox) = Cells(B, kolomnr.van tabel)
    ListBox1.List(a, 4) = Cells(B, 15)                                   '"project nr."         listbox1.List(a, kolomnr.van listbox) = Cells(B, kolomnr.van tabel)
    ListBox1.List(a, 5) = Cells(B, 18)                                   '"normale of overuren" listbox1.List(a, kolomnr.van listbox) = Cells(B, kolomnr.van tabel)
    ListBox1.List(a, 6) = Cells(B, 54)                                   '"Excl.BTW"            listbox1.List(a, kolomnr.van listbox) = Cells(B, kolomnr.van tabel)
        ListBox1.List(a, 6) = Format(ListBox1.List(a, 6), "€#,##0.00")
    ListBox1.List(a, 7) = Cells(B, 55)                                   '"BTW"                 listbox1.List(a, kolomnr.van listbox) = Cells(B, kolomnr.van tabel)
        ListBox1.List(a, 7) = Format(ListBox1.List(a, 7), "€#,##0.00")
    ListBox1.List(a, 8) = Cells(B, 56)                                   '"Incl.BTW"            listbox1.List(a, kolomnr.van listbox) = Cells(B, kolomnr.van tabel)
        ListBox1.List(a, 8) = Format(ListBox1.List(a, 8), "€#,##0.00")

Next a
End Sub

it only works on the sheet where the table is ans when i run the macro.
but i've got a sheet18 "gegevens invoer" with several buttons.
i use that sheet to use the forms.

i can add to my code, activate or select the sheet(dump stats), but i want to stay on sheet18, and not go to there

what code do i need for that.

hope you understand my question...trying to do my best in english

 

[Domenic]

Your macro can be amended as follows...

Private Sub UserForm_Initialize()
 
    With ListBox1
        .ColumnCount = 9
        .ColumnWidths = "50;60;40;175;60;80;70;70;70"
    End With
   
    Dim sourceWorksheet As Worksheet
    Set sourceWorksheet = ThisWorkbook.Worksheets("dump stats")
   
    Dim LstRow As Long
    With sourceWorksheet
        LstRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With
   
    Dim a As Long
    Dim B As Long
    For a = 0 To LstRow - 2
        B = a + 2
        With sourceWorksheet
            ListBox1.AddItem .Cells(B, 2)
            ListBox1.List(a, 1) = .Cells(B, 8).Value                                    '"periode"             listbox1.List(a, kolomnr.van listbox) = Cells(B, kolomnr.van tabel)
            ListBox1.List(a, 2) = .Cells(B, 10).Value                                   '"week"                listbox1.List(a, kolomnr.van listbox) = Cells(B, kolomnr.van tabel)
            ListBox1.List(a, 3) = .Cells(B, 14).Value                                   '"project"             listbox1.List(a, kolomnr.van listbox) = Cells(B, kolomnr.van tabel)
            ListBox1.List(a, 4) = .Cells(B, 15).Value                                   '"project nr."         listbox1.List(a, kolomnr.van listbox) = Cells(B, kolomnr.van tabel)
            ListBox1.List(a, 5) = .Cells(B, 18).Value                                   '"normale of overuren" listbox1.List(a, kolomnr.van listbox) = Cells(B, kolomnr.van tabel)
            ListBox1.List(a, 6) = Format(.Cells(B, 54).Value, "€#,##0.00")
            ListBox1.List(a, 7) = Format(.Cells(B, 55).Value, "€#,##0.00")
            ListBox1.List(a, 8) = Format(.Cells(B, 56).Value, "€#,##0.00")
        End With
    Next a
   
End Sub

You'll notice that I have also declared all variables, and I have specified the Value property for the Range objects in the interest of clarity. Also notice the dots ( . ) before each Cells reference and Rows reference. And, lastly, you'll notice that I have gotten rid of some of the unnecessary lines within the AddItem/List section.

By the way, your English is perfectly fine.

Cheers!

 

[DB73]

Your macro can be amended as follows...

Private Sub UserForm_Initialize()
 
    With ListBox1
        .ColumnCount = 9
        .ColumnWidths = "50;60;40;175;60;80;70;70;70"
    End With
 
    Dim sourceWorksheet As Worksheet
    Set sourceWorksheet = ThisWorkbook.Worksheets("dump stats")
 
    Dim LstRow As Long
    With sourceWorksheet
        LstRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With
 
    Dim a As Long
    Dim B As Long
    For a = 0 To LstRow - 2
        B = a + 2
        With sourceWorksheet
            ListBox1.AddItem .Cells(B, 2)
            ListBox1.List(a, 1) = .Cells(B, 8).Value                                    '"periode"             listbox1.List(a, kolomnr.van listbox) = Cells(B, kolomnr.van tabel)
            ListBox1.List(a, 2) = .Cells(B, 10).Value                                   '"week"                listbox1.List(a, kolomnr.van listbox) = Cells(B, kolomnr.van tabel)
            ListBox1.List(a, 3) = .Cells(B, 14).Value                                   '"project"             listbox1.List(a, kolomnr.van listbox) = Cells(B, kolomnr.van tabel)
            ListBox1.List(a, 4) = .Cells(B, 15).Value                                   '"project nr."         listbox1.List(a, kolomnr.van listbox) = Cells(B, kolomnr.van tabel)
            ListBox1.List(a, 5) = .Cells(B, 18).Value                                   '"normale of overuren" listbox1.List(a, kolomnr.van listbox) = Cells(B, kolomnr.van tabel)
            ListBox1.List(a, 6) = Format(.Cells(B, 54).Value, "€#,##0.00")
            ListBox1.List(a, 7) = Format(.Cells(B, 55).Value, "€#,##0.00")
            ListBox1.List(a, 8) = Format(.Cells(B, 56).Value, "€#,##0.00")
        End With
    Next a
 
End Sub

You'll notice that I have also declared all variables, and I have specified the Value property for the Range objects in the interest of clarity. Also notice the dots ( . ) before each Cells reference and Rows reference. And, lastly, you'll notice that I have gotten rid of some of the unnecessary lines within the AddItem/List section.

By the way, your English is perfectly fine.

Cheers!

thanks for the help...this one works fine for me.

Your macro can be amended as follows...

Private Sub UserForm_Initialize()
 
    With ListBox1
        .ColumnCount = 9
        .ColumnWidths = "50;60;40;175;60;80;70;70;70"
    End With
  
    Dim sourceWorksheet As Worksheet
    Set sourceWorksheet = ThisWorkbook.Worksheets("dump stats")
  
    Dim LstRow As Long
    With sourceWorksheet
        LstRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With
  
    Dim a As Long
    Dim B As Long
    For a = 0 To LstRow - 2
        B = a + 2
        With sourceWorksheet
            ListBox1.AddItem .Cells(B, 2)
            ListBox1.List(a, 1) = .Cells(B, 8).Value                                    '"periode"             listbox1.List(a, kolomnr.van listbox) = Cells(B, kolomnr.van tabel)
            ListBox1.List(a, 2) = .Cells(B, 10).Value                                   '"week"                listbox1.List(a, kolomnr.van listbox) = Cells(B, kolomnr.van tabel)
            ListBox1.List(a, 3) = .Cells(B, 14).Value                                   '"project"             listbox1.List(a, kolomnr.van listbox) = Cells(B, kolomnr.van tabel)
            ListBox1.List(a, 4) = .Cells(B, 15).Value                                   '"project nr."         listbox1.List(a, kolomnr.van listbox) = Cells(B, kolomnr.van tabel)
            ListBox1.List(a, 5) = .Cells(B, 18).Value                                   '"normale of overuren" listbox1.List(a, kolomnr.van listbox) = Cells(B, kolomnr.van tabel)
            ListBox1.List(a, 6) = Format(.Cells(B, 54).Value, "€#,##0.00")
            ListBox1.List(a, 7) = Format(.Cells(B, 55).Value, "€#,##0.00")
            ListBox1.List(a, 8) = Format(.Cells(B, 56).Value, "€#,##0.00")
        End With
    Next a
  
End Sub

You'll notice that I have also declared all variables, and I have specified the Value property for the Range objects in the interest of clarity. Also notice the dots ( . ) before each Cells reference and Rows reference. And, lastly, you'll notice that I have gotten rid of some of the unnecessary lines within the AddItem/List section.

By the way, your English is perfectly fine.

Cheers!

thanks for the help..i appreciate...
for now im gonna try to make 6 comboboxes to use as a filter and copying the filtered lines in the listbox to my invoice sheet