PDA

View Full Version : Populate ListBox with Filtered Records



adamsm
06-06-2010, 04:01 AM
Hi Anyone,

I'm trying to populate a list box with filtered data. I would be happy if I'm told how to do so.

OR else; guided to a location where such kind of data exists.

Any help would be kindly appreciated.

Thanks in advance.

mdmackillop
06-06-2010, 04:09 AM
This (http://www.vbaexpress.com/kb/getarticle.php?kb_id=824) could be used for a unique list, otherwise could you post a sample worbook showing your filter layout and the data required.

adamsm
06-06-2010, 05:45 AM
Thanks for the reply, mdmackillop.

What Iím trying to do is; create a workbook which filters the list box simultaneously as the sheet which populates the list box gets filtered, when a number from the column 2 of the list box is entered in the text box of the user form.

I hope I have made my question clear & I have attached the workbook for better understanding of my problem.

Any help would be kindly appreciated.

mdmackillop
06-06-2010, 09:25 AM
Give this a try

adamsm
06-06-2010, 10:17 AM
Thanks a million, mdmackillop. Your version of the code with the sample workbook works exactly the way as I requested. Except that I could not make the column heads visible in the list box. I did try by changing the following range
Rng
=OFFSET(NewData!$A$4,0,0,COUNTA(NewData!$A:$A),8)
As
Rng
=OFFSET(NewData!$A$5,0,0,COUNTA(NewData!$A:$A),8)

Also by changing the code


Private Sub txtSerialNo_AfterUpdate()
DoFilter
End Sub as

Private Sub txtSerialNo_AfterUpdate()
DoFilter
End With
With Me.ListBox1
.ColumnHeads = True
End If
End Sub
But it didnít work for me.

I would be happy if you could let me know how I may overcome this.
Any help would be kindly appreciated.

Thanks in advance.

mdmackillop
06-06-2010, 10:40 AM
I couldn't get them to show either. I tried using RowSource in the Initialize sub, but that also failed. Maybe a workaround putting labels above the columns, unless someone comes along with a solution.

adamsm
06-06-2010, 10:58 AM
Thanks or the reply. Like you said, for the time being I'll workaround putting labels above the columns, unless someone comes along with a solution for me.

Once again thanks for the help.

shrivallabha
06-06-2010, 11:29 AM
This is what manual says:

ColumnHeads Property

Displays a single row of column headings for list boxes, combo boxes, and objects that accept column headings.

Syntax

object.ColumnHeads [= Boolean]

The ColumnHeads property syntax has these parts:

Part Description
object Required. A valid object.
Boolean Optional. Specifies whether the column headings are displayed.

Settings

The settings for Boolean are:

Value Description
True Display column headings.
False Do not display column headings (default).

Headings in combo boxes appear only when the list drops down.

Remarks

When the system uses the first row of data items as column headings, they can't be selected


So I changed the sub as below:
Sub MakeList()
FinalRow = ActiveSheet.Range("$A$65536").End(xlUp).Row
ListBox1.RowSource = "NewData!A5:H" & FinalRow
ListBox1.ColumnHeads = True
End Sub

But the problem is the listbox does not show filtered list while sheet does :(

adamsm
06-06-2010, 11:58 AM
I also tried that & I'm facing the same problem too.

mdmackillop
06-06-2010, 12:26 PM
On further reading RowSource takes a Data value.
ListBox1.RowSource = "Source", which would be a Range Name

adamsm
06-06-2010, 01:01 PM
Thanks for the help once again mdmackillop & thanks for trying to help me with the code you had provided Shrivallabha. I do really appreciate mdmackillop's & Shrivallabha's response towards my request. Now I can see the column headers in my list box.

mdmackillop
06-06-2010, 01:09 PM
This makes an Advanced Filter copy to another location which allows the Range Name to be used to show the headers. A bit convoluted. Maybe it could be simplified.

adamsm
06-06-2010, 01:15 PM
I'm sorry once again even though suggestion shows the column headers, the list does not get filtered with the worksheet when the code is changed as follows

Sub MakeList()
ListBox1.RowSource = "Data"
End Sub
even with "Rng"

adamsm
06-06-2010, 01:22 PM
Thanks mdmackillop that does the work, even though we have to add an extra sheet to make this code to work .

adamsm
06-13-2010, 03:48 AM
I've added a macro code with a add button. where I'm trying to add data to the row above the selected row from the list box. But I cannot add data when the list box is filtered. For this reason, How could I add the data when the list box gets filtered.

shrivallabha
06-14-2010, 10:33 PM
Does it mean: when the data is added to the worksheet, the listbox does not show updated state. If so, you can call the 'sub' that updates the listbox from the add data 'sub'.

adamsm
06-16-2010, 05:16 AM
Thanks for the help shrivallabha.