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

 

[Domenic]

Try the following code (change and list the desired column headers where specified)...

    Dim rng As Range
    Set rng = ThisWorkbook.Worksheets("dump stats").Range("dumpstats")
   
    Dim filt As Variant
    filt = Application.Filter(rng, Evaluate("ISNUMBER(MATCH(INDEX(" & rng.Rows(1).Address(external:=True) & ",1,0),{""Header2"",""Header4""},0))")) 'change and list the desired column headers accordingly
   
    If Not IsError(filt) Then
        Me.ListBox1.List = filt
    End If

However, why not simply load your listbox with the entire table, and then hide the desired columns? Here's an example..

    'load listbox with entire table
    Me.ListBox1.List = ThisWorkbook.Worksheets("dump stats").Range("dumpstats").Value
   
    'hide columns 2 and 4 by setting the column width to 0
    Me.ListBox1.ColumnWidths = ";0;;0"

And, of course, you can specify the column widths for your other columns. For more details regarding the ColumnWidths property, see the following link...

ColumnWidths property

Office VBA reference topic

learn.microsoft.com

Hope this helps!

 

[DB73]

    'load listbox with entire table
    Me.ListBox1.List = ThisWorkbook.Worksheets("dump stats").Range("dumpstats").Value
   
    'hide columns 2 and 4 by setting the column width to 0
    Me.ListBox1.ColumnWidths = ";0;;0"

with this one i only see 1 column, tried to adjust the ColumnWidths, but that doesnt help

    Dim rng As Range
    Set rng = ThisWorkbook.Worksheets("dump stats").Range("dumpstats")
   
    Dim filt As Variant
    filt = Application.Filter(rng, Evaluate("ISNUMBER(MATCH(INDEX(" & rng.Rows(1).Address(external:=True) & ",1,0),{""Header2"",""Header4""},0))")) 'change and list the desired column headers accordingly
   
    If Not IsError(filt) Then
        Me.ListBox1.List = filt
    End If

this one doesnt show anything , tried to change the "Headernames" ???

sorry, i dont get it

 

[Domenic]

with this one i only see 1 column, tried to adjust the ColumnWidths, but that doesnt help

Let's say that you have 5 columns, and that you want to set the width of Columns 1, 3, and 5 to 50, and you want to hide Columns 2 and 4, you would set the column widths like this...

Me.ListBox1.ColumnWidths = "50;0;50;0;50"

So as you can see, the first column is set to 50, the second column is set to 0 and hides the column, the third column is set to 50, etc. You would just have to adjust the setting so that it reflects the actual column widths you want, along with the columns you want to hide.

this one doesnt show anything , tried to change the "Headernames" ???

Does Range("dumpstats") include the header row?

 

[DB73]

got it working now with

Private Sub UserForm_Initialize()
With ListBox1
    .ColumnCount = 6
    .ColumnWidths = "50;50;50;50;50;50"

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)    'listbox1.List(a, kolomnr.van listbox) = Cells(B, kolomnr.van tabel)
    ListBox1.List(a, 1) = Cells(B, 8)
    ListBox1.List(a, 2) = Cells(B, 10)
    ListBox1.List(a, 3) = Cells(B, 14)
    ListBox1.List(a, 4) = Cells(B, 15)
    ListBox1.List(a, 5) = Cells(B, 18)
 

Next a
End Sub

onlything, it works when the "dumpstats" is open.
now i'm trying to get it working in my sheet with all my buttons that opens the userforms
got a button on sheet "gegevens invoer", that opens the userform, but doesnt load the "dumpstats" into the listbox
so, for now im trying to do my best to get it working, i have to define that it has to load the dumpstats into listbox even when im on another sheet.

im new with VBA...ist hard working for me

 

[DB73]

Let's say that you have 5 columns, and that you want to set the width of Columns 1, 3, and 5 to 50, and you want to hide Columns 2 and 4, you would set the column widths like this...

Me.ListBox1.ColumnWidths = "50;0;50;0;50"

So as you can see, the first column is set to 50, the second column is set to 0 and hides the column, the third column is set to 50, etc. You would just have to adjust the setting so that it reflects the actual column widths you want, along with the columns you want to hide.

i found in the listbox propeties i had to set the columns to 5 or 6, just what i need, after that its works

 

[DB73]

Does Range("dumpstats") include the header row?

does Range("dumpstats") include the header row?

i mean this ?

 

[Domenic]

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!

 

[Phuynh]

Use data valiation list without VBA code!

Phh

 

[Fluff]

Use data valiation list without VBA code!

How is that going to help with multiple columns?