Populate ListBox with Filtered Records

[adamsm]

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]

This could be used for a unique list, otherwise could you post a sample worbook showing your filter layout and the data required.

[adamsm]

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]

Give this a try

[adamsm]

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]

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]

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]

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:
[VBA]Sub MakeList()
FinalRow = ActiveSheet.Range("$A$65536").End(xlUp).Row
ListBox1.RowSource = "NewData!A5:H" & FinalRow
ListBox1.ColumnHeads = True
End Sub[/VBA]

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

[adamsm]

I also tried that & I'm facing the same problem too.

[mdmackillop]

On further reading RowSource takes a Data value.
ListBox1.RowSource = "Source", which would be a Range Name

[adamsm]

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]

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]

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]

Thanks mdmackillop that does the work, even though we have to add an extra sheet to make this code to work .

[adamsm]

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]

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]

Thanksfor the help shrivallabha.