Multiple Column Listbox (with headers) = the bain of my existence

[IRish3538]

So I've been trying to for a while now to construct a listbox in a userform. The data is tabled in another workbook. I have no prolem populating the listbox however I'm having major issues with the column headers.

There are different ways I can populate the listbox from the table.. referencing the 'Table', creating an array, or thru the 'Rowsource' property. Apparently, column headers only work if the listbox is populated using a rowsource. Fine. whatever. But everytime I populate the listbox using that method, I get this nagging resource error. Even when I close out the other workbook and set it to nothing, cutcopymode = false. the listbox data is obviously still linked causing the error. Any work around here? How can i get this data into the listbox, break the linkage, and still have column headers. The table is a test table right now and is only 4 rows long.

Would my best option be to physically copy the table from the outside wb to the main wb, then source the data there? I'm reluctant to do this because it isn't very clean.

Any help would be greatly appreciated!!! Here's my code. I cut out all of the error handlers and other stuff to keep it simple.

Sub Pop_Listbox()
    Dim wb As Workbook
    Dim ws As Worksheet
        
    fldr = "c:\"
    sheetname = "test.xls"
    
    Set wb = Application.Workbooks.Open(fldr & sheetname, False, True)
    Set ws = wb.Sheets(1)
    
    'Get the number of rows
        data_rows = ActiveWorkbook.Sheets("Database").Cells(Rows.Count, 1).End(xlUp).Row
        
    'This sets the rowsource range... Starts at row 2 so Excel can recognize the headers
       Set table_range = ws.Range(Cells(2, "A"), Cells(data_rows, "g"))
    
    'Sets the 'Rowsource' for the table and sets the number of columns
       uf_List.lb_list.RowSource = table_range.Address
       uf_List.lb_list.ColumnCount = 7
    
    'My attempt to clean it up
        wb.Close
        Set wb = Nothing
        Application.CutCopyMode = False


End Sub

[Kenneth Hobs]

Have you considered doing two listboxes where one is just the header row?

[IRish3538]

Yeah I tried that but I'm looking for a cleaner way to do it. Plus, the list box scrolls horizontally which makes it a nightmare.

For right now I just copied over the table into the main workbook and filled the listbox by sourcing that. It seems to work fine. I more or less just want to know how to sever the rowsource linkage after the listbox is populated (assuming the listbox would even stay populated at that point)

[p45cal]

Perhaps a ListView control might not have the same problems?
Msg#4 here: http://www.mrexcel.com/forum/excel-q...rid-lines.html
a long shot.

[Kenneth Hobs]

The usual course is to place the data in the userform's workbook in a hidden sheet. There are several ways to do it. Depending on the data, that process is not excessively slow.

We could probably find an API method to sync the two listboxes if you are interested.

[snb]

What's wrong with:

Private Sub UserForm_Initialize()
    With ListBox1
       .List = Sheets("totaal").Cells(1).CurrentRegion.Value
       .ColumnCount = UBound(.List) + 1
       .ColumnWidths = Replace(String(.ColumnCount, ";"), ";", "60;")
    end with
End Sub