Populate ListBox with all columns

[adam]

Hi,

I'm having a worksheet where I have data rows starting from row 11, which means the column headers are in row 10. The columns associated with data rows are from column "E" to "K".

I do have a list box which i've createdusing the forms control in worksheet "List".

The range that I've created for the list box is
=OFFSET(CustomerList!$E$10,0,0,COUNTA(CustomerList!$E:$E),7)

When this formula is applied in the input range of the list box, I get only the column "E" not but not the remaining 6 columns.

Why is this?

Note: I haven't applied anything in cell link.

[HansV]

A list box or combo box from the Forms controls has only one column.
You need to use a list box or combo box from the ActiveX controls and to set its ColumnCount property to 7.

[adam]

I'm using a list box, but not a com box from the forms control.
Do you mean even this list box will display only one column?

And if I want to display all the columns I have to use a list box control from the ActiveX controls and to set its ColumnCount property to 7?

[HansV]

Sorry, I originally mentioned combo boxes, but the same holds for list boxes. (I have edited my previous reply)

[adam]

Yeah. I knew you did miss the "Listbox" in your first reply. Anyway, thanks for the reply and help.

[adam]

Instead of the worksheet, I've applied the list box on a the user form. But it takes time for the user form to load with more data rows.

How can I speed up the user form?

[HansV]

You fill the list box twice in the UserForm_Initialize event procedure: first by calling FilterList, then by calling DoFilter, which in its turn calls FilterList again. There is no need to call DoFilter here, since the combo box and text box are still blank.

[adam]

Here's the workbook after removing the suggested and any other. Does not seem to have any difference.

[HansV]

The userform loads about twice as fast as in the earlier version.

Keep in mind that Excel is not a database application.

[adam]

Thanks for the reply.