Let’s consider a report sheet summarizing the performance of a network of computer clubs, placed on the worksheet Vedomost in an Excel workbook. When the workbook opens, there should be a chart next to the data that provides a visual representation of the performance dynamics of a specific club. This must be achieved by allowing the user to select a particular club from a list of clubs. Moreover, selecting a club from the list should change the data range on which the chart is based and trigger the chart to update accordingly.
To implement this project, perform the following steps.
- Assign, for example, the name Vedomost to the first worksheet.
- On this worksheet, prepare a tabular report summarizing the performance results of the computer clubs.
- Create a list on the worksheet and, using the Properties window, set its Name property to Club.
- In the ThisWorkbook module, enter the required code: when the workbook opens, the Workbook_Open procedure runs, which deletes all charts from the Vedomost worksheet, builds a 3-D column chart based on the performance results of the “Altair” computer club, and positions it so that it occupies the range G7:P26. This ensures that the chart does not overlap the data table. The procedure also populates the list based on the header row of the data table and selects the first item in this list.
- In the code module of the Vedomost worksheet, enter an event procedure to handle the list’s Click event so that when a club is selected from the list, the chart is rebuilt.