Excel 2007 ,VBA form to filter worksheet records based on condition

I have a list of records that I want to work with it through VBA form.

for ex: below records list

ID          Score   Date  
------------------------------
228483      80      5/10/2011  
243216      60      4/3/2011  
285483      40      5/1/2011  
291584      30      5/2/2011  
291323      70      4/4/2011  
285607      40      5/7/2011  
7158441     90      5/8/2011  
7083234     10      4/2/2011  

I would like to have a vba form having multi form components which each will list the records from this above list based on condition.

for example on the top left the component will list records who scored over 50, and on the top right another component from the same type will list records achieved during month# 5.

I 'm not that much in VBA forms and really don't know what component will do that and how.

Will any changes in the worksheet be reflected directly to the lists in the vba form?


Thanks brother Jonsca and harag for the great support and yes I'm looking something like DataGridView that is a scrollable and sortable table retreived from database based on condition..I tried to look for "Microsoft Office Spreadsheet" but it seems it need some work around for excel2007 to add it but my question is , could we have this in simpler way for example a local web page could be connected to excel sheet and have the same control in it....I made a sketch for what i'm looking for

Is it easier to build a danamic connection between web page to worksheet (and with having this control)...big thanks brothers in advance.

Answers


I would suggest you use a listbox control. The listbox can display several columns, you just need to populate it. Something like the below should help

'clear the listbox
lst.Clear

' set the number of columns
lst.ColumnCount = 5

' iterate through the cells and populate the listbox
For x = 1 to 10
    With lst
        .AddItem "ID"                       ' Column 1 data
        .List(.ListCount - 1, 1) = "Score"  ' Column 2 data
        .List(.ListCount - 1, 2) = "Date"   ' Column 3 data
        .List(.ListCount - 1, 3) = "Col 4"  ' Column 4 data
        .List(.ListCount - 1, 4) = "Col 5"  ' Column 5 data
        'etc.
    End With
Next

' set the widths of the columns
lst.ColumnWidths = "2.0cm;1.0cm;2.0cm;1.0cm;1.0cm"

You will need to pull in the values you want accordingly

Hope this helps

EDIT: >>Will any changes in the worksheet be reflected directly to the lists in the vba form?<< No, you will need to update the listbox through VBA code.


Need Your Help


About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.