Let’s consider the creation of an application that filters data for a specified column in a data list.
First, create a workbook and rename Sheet1 to List. Delete the remaining worksheets of the workbook. Place the prepared table of data about cars and their owners on the List sheet.
When you right-click the table header, a context menu should appear with a single command: Filter. Selecting this command should call a dialog box asking for the address of the header cell of the data list. After specifying the cell address, a form should appear with a list box containing the values from that column and a check box labeled Filter.
- If the check box is selected, the data on the worksheet will be filtered by the value chosen in the list.
- If the check box is cleared, the filter will be removed.
Now, create a form and place on it a ListBox and a CheckBox. Using the Properties window, set the Name property of the form to frmFilter.
Enter the corresponding code in the form module, in the ThisWorkbook module, and in a standard module.
In the Form Module
There are three procedures:
- Form Initialize event procedure — sets the Caption properties of the check box and form, and fills the list. The list is filled with values from the column of the table whose first cell address is entered by the user. Duplicate values are skipped during filling.
- ListBox Change event procedure — when an item is selected in the list, if the Filter check box is selected, the table is filtered. If AutoFilter arrows have not yet been displayed in the table, this procedure first initializes AutoFilter and then performs filtering.
- CheckBox Change event procedure — depending on the state of the check box, either performs filtering (by calling the list box Change event procedure) or removes AutoFilter.
In the ThisWorkbook Module
There are three procedures:
- Workbook Open event procedure — creates a context menu with a single item Filter, linked to the procedure DoFilter.
- Workbook SheetBeforeRightClick event procedure — displays the context menu when the user right-clicks the first row of the List sheet.
- Workbook BeforeClose event procedure — deletes the context menu from the workbook when the workbook is closed.
In the Standard Module
There is a single procedure DoFilter, which displays the Field_Name window.