Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to bind filtered data in list box?
Hi All,
I have developed a from in excel 2003 contains year dorpdown and listbox. The list box source mapped/binded to a ragne and 'Year' dropdown has unique years from the same range of listbox source. My requirement is, if user selects year through 'Year' dropdown then the list box has to refresh with selected year's data only. I tried to filter the source data with selected 'Year' value but the listbox has been displaying with old records i.e., still I see whole data includes hidden rows after applying filter on source data. I should not see the hidden rows in list box. Is there any best method than implented or how to hide list box with hidden rows from source range? Please share your knowledge or thourghts in overcomeingt the issue which will be appreciated. Advanced Thanks, ~Vins. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to bind filtered data in list box?
You'll have to drop the rowsource for the year listbox.
Then you could loop through the filtered range and check to see if the row is hidden. If it is visible then include it in the code to add the item to the listbox -- or you could just look through the visible rows in that filtered range... Option Explicit Sub testme() Dim wks As Worksheet Dim VisRng As Range Dim myRng As Range Dim myCell as range Set wks = ActiveSheet me.listbox1.clear 'remove any existing list. With wks 'just a single column. I used column A. Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)) 'remove any existing filter .AutoFilterMode = False myRng.AutoFilter Field:=1, Criteria1:="whateveryouneeded" With .AutoFilter.Range.Columns(1) If .Cells.SpecialCells(xlCellTypeVisible) = 1 Then MsgBox "only header visible" Else 'avoid the header Set VisRng = .Resize(.Cells.Count - 1).Offset(1, 0) for each mycell in visrng.cells me.listbox1.additem mycell.value next mycell End If End With .AutoFilterMode = False 'remove the filter End With End Sub Untested, uncompiled. Watch for typos! =============== Or you could drop the autofilter and just loop through the cells. If they match a certain criteria, then use .additem to add it to the listbox. Vinod wrote: Hi All, I have developed a from in excel 2003 contains year dorpdown and listbox. The list box source mapped/binded to a ragne and 'Year' dropdown has unique years from the same range of listbox source. My requirement is, if user selects year through 'Year' dropdown then the list box has to refresh with selected year's data only. I tried to filter the source data with selected 'Year' value but the listbox has been displaying with old records i.e., still I see whole data includes hidden rows after applying filter on source data. I should not see the hidden rows in list box. Is there any best method than implented or how to hide list box with hidden rows from source range? Please share your knowledge or thourghts in overcomeingt the issue which will be appreciated. Advanced Thanks, ~Vins. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Bind a textbox to Access data | Excel Discussion (Misc queries) | |||
Getting data from a filtered list | Excel Discussion (Misc queries) | |||
bind data to active x listbox without macro | Excel Discussion (Misc queries) | |||
Select data in filtered list | Excel Discussion (Misc queries) | |||
Drop down list from filtered data | Excel Discussion (Misc queries) |