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.
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.