PDA

View Full Version : Multiple Column Listbox (with headers) = the bain of my existence



IRish3538
03-21-2014, 01:02 PM
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
03-21-2014, 01:17 PM
Have you considered doing two listboxes where one is just the header row?

IRish3538
03-21-2014, 01:25 PM
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
03-21-2014, 04:54 PM
Perhaps a ListView control might not have the same problems?
Msg#4 here: http://www.mrexcel.com/forum/excel-questions/581015-list-box-grid-lines.html
a long shot.

Kenneth Hobs
03-21-2014, 06:37 PM
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
03-22-2014, 06:33 AM
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