Filling Listbox with more than 10 columns with array made from table with hidden columns

Hi,

I have a worksheet(code name: "ServLogSheet") having transactions table named "ServLogTbl". The table has 20 columns.

I need to fill up a listbox (lbCustLog) to display only some of the columns from the table.

I used the below code to do it. This worked till the number of columns to be displayed was up to 10.

Please Login or Register  to view this content.

Now, I need to display more than 10 columns from the ServLogTbl. Based on my search I found that the limit of listbox property columncount = 10 can be overcome if I use an array to fill the listbox using .list

I can hide the not to be displayed columns of the ServLogTbl in the code. How do I select the visible table to fill an array? I tested the below code but only the first column of the table was filled in the array.

Please Login or Register  to view this content.

With the same logic : Apart from hiding columns, can I also apply filters to the table columns and fill an array with the visible part of the table.

See if the attached example helps.
If you want to hide some column in ListBox just put in "ListBox1.ColumnWidths", zero value for that column

Adapted from one of my previous contributions.
Some of the code/approach may be of assistance.
Sheet 2 is used as a temporary sort storage.
torachan.

JCabral, Thanks. It helps. Like the idea of setting the column with to 0 in listbox. Makes it easy to filter out columns.

I am using labels on top of the listbox as headers for the listbox columns. The normal ColumnHeads property doesn't work as I am not using the RowSource to populate the listbox. Now for cases where my listbox width is not enough to display all the columns, i.e. when there is a horizontal scroll bar in the listbox , how do I get the Column headers? Is there any workaround?

Originally Posted by hemantparmar

JCabral, Thanks. It helps. Like the idea of setting the column with to 0 in listbox. Makes it easy to filter out columns.

I am using labels on top of the listbox as headers for the listbox columns. The normal ColumnHeads property doesn't work as I am not using the RowSource to populate the listbox. Now for cases where my listbox width is not enough to display all the columns, i.e. when there is a horizontal scroll bar in the listbox , how do I get the Column headers? Is there any workaround?

hemantparmar

See if this help

Thanks JCabral. It works perfect.

Can I request you to look at another thread which is for the same project of mine. It is about Creating a new Array(smaller) from the original Array depending on some filter condition. I know how to manage the filter conditions(using multiple IFs). I need help on creating a smaller array from a Bigger Array.

https://www.excelforum.com/excel-pro...ml#post5224305

regarding your post #8, look at the method used in my post #5

@JCabral,

There is a challenge I am facing with the below code of populating the listbox.

Please Login or Register  to view this content.

This code is very much useful for populating more than 10 columns, hiding unwanted columns and setting column heads. However, it gives it gives "unspecified error (80004005)". And I think the reason is as follows.

I am using Advanced Filter method to copy part of the full data table in a new sheet, and defining that filtered range(lbRange) as the range for list box in this line .RowSource = lbRange.Address(external:=True) of the above code.

This works fine for the first instance of this code.

There are multiple combo boxes that act as filters on the user form. Every time a combobox value changes, the Advanced Filter method is applied to get a new set of range to be filled in the listbox. And I think that's where RowSource method doesn't work. I think when RowSource is used you can not change that data on worksheet during the code.

Below is the full code for the sub that gets called for every change in the various combo box value. I get error on the first two line itself - unspecified error (80004005).

ServLogTbl is the full Data Table on ServLogSheet.
I have added one more sheet called ServLogFiltSheet, to define the Advanced filter criteria range and also to give a CopyToRange as cell A4 on it.

Please Login or Register  to view this content.

The reason you suggested me to use the rowsource method of filling the list box was to accommodate the ColumnHeads.

Is there any other way to achieve this:
1. Get more than 10 columns & Column Heads (for listbox having horizontal scroll bar)
2. Allow repopulating of listbox based on changes in multiple combo box values - Advanced filter is the best option for me to do this.
3. The other option is to store the new range obtained from Advanced filter in an array and populate the listbox using .list = NewArray. BUT can I get the Column Heads in this case?

Rowsource is the only method of populating listboxes with headers and it is the worst method to use and is only appropriate to use in low level apps.

http://www.snb-vba.eu/VBA_Fill_combobox_listbox_en.html

This was solved as follows. With few simple steps. Hope this would be useful for someone.

@JCabral: used column width to zero for unwanted list columns
@Torachan: used the array and .List method

Apart from the above two methods, I managed to keep the column heads (external labels) by going back to basics: increase the userform size. Used almost the full screen size(width 955) and reducing the column widths to bare minimum.

Please Login or Register  to view this content.