​Excel VBA Report Writing and Image Control

Dashboard Reports Based On User County and Image Selections

​This example workbook demonstrates how Excel can be extended to have report writing and image movement features based on user choices. It combines actual features I implemented into Client work and demonstrated here with sample images and data.

This sample shows various information about counties in Wisconsin. The user is able to either change the county name with a Data Validation list or change the type of map they are currently looking at using radio button controls.

​When changing the county name, VLOOKUP formulas are used to change the Historical Population as well as the Presidential Election Results. While the VLOOKUP formula traditionally uses only one criteria in its parameters, these use a special dual criteria setup to gather both county name and year information from the data table, which is hidden on another worksheet.

Second, the 2000 Census Age Pyramid image is changed. This is a very similar process to how the county map is changed.

VBA code is also triggered when the county name is changed. At this time three different VBA procedures run. First, the image of the county map is changed. Which image is shown also depends on the radio buttons requesting the user to choose a County map or a Location in State map.

Lastly, VBA code must list the different municipalities and group them by type. This cannot be done easily or efficiently by formulas. Instead VBA works through a set of data and lists all of those municipalities associated with the chosen county. The code is able to determine how to group the municipality types as well, since some counties have different types than others.

While the data in this example file is limited, it demonstrates features created for multiple clients (Excel VBA report writing, complex formula implementation and image changes). These features are dependent on Excel VBA code as it relates to Event Handling. All clients using some or all of these features were very satisfied with the added functionality brought to their Excel projects.

The file can be downloaded below (you will need to enable macros and enable editing ​in Excel):