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
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