Refresh RowSource Visible Data

[Cander]

How can you make a ListBox RowSource show only visible data from a filtered range and then when the filtered range changes with new filter criteria, show the new visible data?
I Have a Defined Name Range named “Quantity” with the code:
=OFFSET(BuildingMaterial!$B$2,0,0,COUNTA(BuildingMaterial!$B:$B),5)
The Range “Quantity” is filtered with a ComboBox selection with the code:
Private Sub ComboBox24_Change()
Worksheets("BuildingMaterial").Range("M1").Value = ComboBox24.Value
Sheets("BuildingMaterial").Select
ActiveSheet.Range("Combo").AutoFilter Field:=1, Criteria1:=RGB(0, _
176, 240), Operator:=xlFilterCellColor
Sheets("Tom").Select
End Sub
The ListBox is a 5 column MultySelect ListBox on a MultiPage, on a UserForm named ListBox42.
With the RowSource: =Quantity
Right now my ListBox shows all visable and hidden data in the filtered Range “Quantity”.

 

[Bill Rockenbach]

Instead of using a dynamic array you could load the filtered data with the additem method.

    Dim rng As Range
    Dim c As Range
    Set rng = rng.Cells.SpecialCells(xlCellTypeVisible)
    With Me.ListBox42
        .Clear
        'Build workaround List Box Header
        .AddItem "Row#"
        .List(.ListCount - 1, 1) = Sheet1.Cells(6, 1).Text
        .List(.ListCount - 1, 2) = Sheet1.Cells(6, 6).Text
        .List(.ListCount - 1, 3) = Sheet1.Cells(6, 7).Text
        .List(.ListCount - 1, 4) = Sheet1.Cells(6, 10).Text
        .List(.ListCount - 1, 5) = Sheet1.Cells(6, 11).Text
        'Add rows to the list box
        For Each c In rng
            .AddItem c.Row
            .List(.ListCount - 1, 1) = c.Value
            .List(.ListCount - 1, 2) = c.Offset(0, 5).Value
            .List(.ListCount - 1, 3) = c.Offset(0, 6).Value
            .List(.ListCount - 1, 4) = c.Offset(0, 9).Value
            .List(.ListCount - 1, 5) = c.Offset(0, 10).Value
        Next c
    End With

 

[Cander]

Thank You for your quick response.
I would like to try the additem method!
I am still to new to VBA to understand where to put your code.
Can you help me with the codes location?
Thanks Charlie

 

[Bill Rockenbach]

Option Explicit
Private Sub ComboBox24_Change()
Dim rng As Range
Dim c As Range
'Filter the sheet
Worksheets("BuildingMaterial").Range("M1").Value = ComboBox24.Value
Sheets("BuildingMaterial").Select
ActiveSheet.Range("Combo").AutoFilter Field:=1, Criteria1:=ComboBox24.Value
'Populate Listbox42 with filtered range
Set rng = Range("a2", Range("a65536").End(xlUp))
Set rng = rng.Cells.SpecialCells(xlCellTypeVisible)
With Me.ListBox42
.Clear
'Build workaround List Box Header
.AddItem "Row#"
.List(.ListCount - 1, 1) = Sheet1.Cells(1, 1).Text
.List(.ListCount - 1, 2) = Sheet1.Cells(1, 2).Text
.List(.ListCount - 1, 3) = Sheet1.Cells(1, 3).Text
.List(.ListCount - 1, 4) = Sheet1.Cells(1, 4).Text
.List(.ListCount - 1, 5) = Sheet1.Cells(1, 5).Text
'Add rows to the list box
For Each c In rng
.AddItem c.Row
.List(.ListCount - 1, 1) = c.Value
.List(.ListCount - 1, 2) = c.Offset(0, 1).Value
.List(.ListCount - 1, 3) = c.Offset(0, 2).Value
.List(.ListCount - 1, 4) = c.Offset(0, 3).Value
.List(.ListCount - 1, 5) = c.Offset(0, 4).Value
Next c
End With
Sheets("Tom").Select
End Sub
 
Private Sub UserForm_Terminate()
Worksheets("BuildingMaterial").AutoFilterMode = False 'To remove filter
End Sub

 

[Bill Rockenbach]

For some reason my explanation did not post with the previous code sample so here it is again
I assume you want the list box populated immediately after the MuldingMaterial sheet is filtered.
I’m using xl2000 which does not support color filtering. For testing I filtered Column A of the BuildingMaterial sheet based on the value of the combobox (assumed a part number).
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I added to the listbox the row number and the first 5 columns of each row of the BuildingMaterial sheet that was filtered. You can change this to whatever data you want in the listbox.
<o></o>
That makes 6 columns in the ListBox. (You can have a maximum of 10 columns in a list box using xl2000) The reason I added the row number is if you select an item in the list box you can retrieve the data for the item selected using the row number as reference.
<o></o>
Set the following properties of ListBox42:
ColumnCount = 6 or how many columns you use including the row number if you add in the row number
CoulmnHead set to False
ColumnWidth set to the appropriate width of each column. For columns you do not want displayed (such as the row number) enter a column width of zero (0).
<o></o>
The code goes in the code module of your userform.

[FONT=Times New Roman] 
Option Explicit
Private Sub ComboBox24_Change()
    Dim rng As Range
    Dim c As Range
 <o:p></o:p>
    'Filter the sheet
    Worksheets("BuildingMaterial").Range("M1").Value = ComboBox24.Value
    Sheets("BuildingMaterial").Select
 <o:p></o:p>
            ‘Change this line back to filter by color if you wish
    ActiveSheet.Range("Combo").AutoFilter Field:=1, Criteria1:=ComboBox24.Value
 <o:p></o:p>
    'Populate Listbox42 with filtered range
    Set rng = Range("a2", Range("a65536").End(xlUp))
    Set rng = rng.Cells.SpecialCells(xlCellTypeVisible)
    With Me.ListBox42
        .Clear
        'Build workaround List Box Header
        .AddItem "Row#"
        .List(.ListCount - 1, 1) = Sheet1.Cells(1, 1).Text
        .List(.ListCount - 1, 2) = Sheet1.Cells(1, 2).Text
        .List(.ListCount - 1, 3) = Sheet1.Cells(1, 3).Text
        .List(.ListCount - 1, 4) = Sheet1.Cells(1, 4).Text
        .List(.ListCount - 1, 5) = Sheet1.Cells(1, 5).Text
        'Add rows to the list box
        For Each c In rng
            .AddItem c.Row
            .List(.ListCount - 1, 1) = c.Value
            .List(.ListCount - 1, 2) = c.Offset(0, 1).Value
            .List(.ListCount - 1, 3) = c.Offset(0, 2).Value
            .List(.ListCount - 1, 4) = c.Offset(0, 3).Value
            .List(.ListCount - 1, 5) = c.Offset(0, 4).Value
        Next c
    End With
 <o:p></o:p>
    Sheets("Tom").Select
End Sub
 <o:p></o:p>
 <o:p></o:p>
Private Sub UserForm_Terminate()
Worksheets("BuildingMaterial").AutoFilterMode = False 'To remove filter
End Sub
[/FONT]

 

[Cander]

Hi Bill
The filter works great and it changes the listbox with the combobox change perfectly : the row# are for the right filtered rows. I adjusted Sheet1 to Sheet2 (BuildingMaterial) and tried to adjust the cell locations but am not having much luck. My data is located in “B:E” and I also have column heads.
Can you help me with the data location and column heads please.
Thank you so much
Charlie

 

[Bill Rockenbach]

the second number in the Cells object is the row number
teh second number in the Offset is the nubmer of rows over from teh range 'c'

Change the listbox CountColums property to 5

    With Me.ListBox42
        .Clear
        'Build workaround List Box Header
        .AddItem "Row#"         'Listbox list items start at 0
        .List(.ListCount - 1, 1) = Sheet1.Cells(1, 2).Text 'Col B
        .List(.ListCount - 1, 2) = Sheet1.Cells(1, 3).Text 'Col C
        .List(.ListCount - 1, 3) = Sheet1.Cells(1, 4).Text 'Col D
        .List(.ListCount - 1, 4) = Sheet1.Cells(1, 5).Text 'Col E
        'Add rows to the list box
        For Each c In rng
            .AddItem c.Row
            .List(.ListCount - 1, 1) = c.Offset(0, 1).Value 'Col B
            .List(.ListCount - 1, 2) = c.Offset(0, 2).Value 'Col C
            .List(.ListCount - 1, 3) = c.Offset(0, 3).Value 'Col D
            .List(.ListCount - 1, 4) = c.Offset(0, 4).Value 'Col E
        Next c
    End With

 

[Cander]

Hi Bill
Tried different row numbers. Still no column heads in the listbox.
Everything else works perfectly.
Thank you
Charlie

 

[Bill Rockenbach]

Normally if you have a specified range or a range name in the ListBox RowSource property and the ColumHeads property set to True then the first row of the RowSource range will be displayed as a header incased in grid lines at the top of the list box window. This header cannot be selected from the list box.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Since the AddItem method is being used to add items to the list box, Excel cannot create a header row in the list box because there is no RowSource range from which to pick the first row as a header.
<o></o>
The “workaround header” code adds the Header items to ListIndex zero (0) in the ListBox as data items using the AddItem method. If the ListBox ColumnHeads property is set to True, Excel will display blank grid line boxes. The CountHeads property must be set to False.
<o></o>
The workaround header will not be incased in grid lines, as do the true ListBox Headers. The only way I know of to help identify the workaround header as headings is to use all upper case for the headings.
<o></o>
Now, since the workaround heading is actually a regular list item, not a true ListBox heading, it is available to be selected. To get around this, use this code as the first line of code in any of the ListBox Click events, an attempt to select the workaround header will be ignored.
<o></o>

If Me.ListBox42.ListIndex = 0 Then Exit Sub  'ignore workaround Header selected

<o></o>
The code I suggested expects your header items to be in row number 1 of the sheet. I have tested the code and it works as designed.
<o></o>
What exactly is showing up in the ListBox window?

 

[Cander]

Thanks Bill
You’ve been great
Your additem method works great and has solved my problem with rowsource.
Thanks again
Charlie