![]() ![]() This form displays two datasets… the contacts based in a particular city (lstData), and a unique list of cities extracted from that data using the Excel vba advanced filter (lstCities). subforms based on subforms, each filter involving a sql query against for example a backend sql server database to drive each section of the form, and … it worked like a charm! The DetailsĬreate a form similar to the example below, name the listbox on the left lstCities and the one to the right, lstData However in the past I’ve created hirearchical Excel vba forms for clients with many levels of filtering i.e. ![]() In the example above I’ve used some static data in a hidden sheet, combined with vba driving the advanced filter to create sublists on a new sheet based on the city criteria. In the listbox above (Figure 1), the process I’ve just described drives the initial loading of the form, which you can see in the dynamic image at top when the "Clear Filter" button is clicked. The above image, shows a range within a hidden Excel worksheet / tab, this could be the result of the CopyFromRecordset() method in the above code snippit. Figure 2:- Hidden Datasource worksheet, drives the lstData listbox on form So in the case below (Figure 2) the data could have been returned from a Sql Server query to cell A4 of the hidden worksheet (vba code would clear this spreadsheet data prior to writing new data from Sql Server). Get the coordinates of your new "splatted" range and apply these to the rowsource of the listbox. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
January 2023
Categories |