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.
Populate ListBox with all columns
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Populate ListBox with all columns
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Populate ListBox with all columns
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.
You need to use a list box or combo box from the ActiveX controls and to set its ColumnCount property to 7.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Populate ListBox with all columns
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?
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?
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Populate ListBox with all columns
Sorry, I originally mentioned combo boxes, but the same holds for list boxes. (I have edited my previous reply)
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Populate ListBox with all columns
Yeah. I knew you did miss the "Listbox" in your first reply. Anyway, thanks for the reply and help.
Best Regards,
Adam
Adam
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Populate ListBox with all columns
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?
How can I speed up the user form?
You do not have the required permissions to view the files attached to this post.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Populate ListBox with all columns
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.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Populate ListBox with all columns
Here's the workbook after removing the suggested and any other. Does not seem to have any difference.
You do not have the required permissions to view the files attached to this post.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Populate ListBox with all columns
The userform loads about twice as fast as in the earlier version.
Keep in mind that Excel is not a database application.
Keep in mind that Excel is not a database application.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07