With a few simple lines of Excel VBA your static Excel reports can become dynamic. A single click shows or hides charts, objects, or rows and columns. Dynamic Excel reports give your users the ability to show or hide specific parts of reports, such as selected sales regions or to expand a table to show additional detail or trend charts. It makes reports look professional, gives your users flexibility, and it takes little additional work on your part.
Operating the example dynamic Excel report is as easy as clicking. Click on a check box at the top of the worksheet and the associated part of the report shows or hides. This works for rows, charts, and images.
How this works is straightforward.
1. Clicking a check box does two things. Firstly, it changes the result for the check box to TRUE (selected) or FALSE (unselected). These TRUE/FALSE results appear in a named cell. Secondly, clicking each check box runs a VBA macro assigned to it.
2. The macro tests whether the check box is TRUE (selected) or FALSE (unselected).
3. If TRUE, the macro runs a few lines of code to display a named range, a chart, or an image. If FALSE, the macro runs a few lines of code to hide a named range, a chart, or an image.
To examine the minimal lines of code that run this show/hide Excel report, download the example file and then press Alt+F11 to open the VBA Editor.
The VBA Editor shows you the simple code used in this dynamic Excel report.
Each object and range the macro works on has a name. In Excel 2013/2016/365 you can display the Object Selection Pane to see the object names by pressing Alt+F10. In this pane you can also click to show/hide an object or edit its name. Be careful, if you change an object’s name in the Object Selection Pane you must change the name in the macro to match.
Range names are used in the worksheet to store the TRUE/FALSE selection from check boxes and to identify the rows (or columns, if you wish) that will show or hide.
You can select cells and rows that are assigned named ranges by pressing F5, selecting a range name, and clicking Ok. This dialog box shows you the range names used in the worksheet and used by the macro.
Named ranges are easier to work with if you create them with a prefix that identifies what they are. In these range names “rsh” is the prefix for rows that will show or hide. “slct” is the prefix for named ranges used to hold the TRUE/FALSE result from a selected check box.
To learn why and how to use prefixes with named ranges, see the article, “Use a Structured Prefix to Identify Range Name Types.”
The range names used for this dynamic report are global,
|rshChartRows||R11:R19||Rows underlying the chart|
|rshNonDigital||R21:R27||Rows underlying Non-Digital data|
|rshDigital||R29:R34||Rows underlying Digital data|
Ranges and objects on the worksheet are shown or hidden by clicking the appropriate check box. The resulting TRUE/FALSE from each check box goes in the check box’s cell in H2:H5. Each of these cells has a range name shown to its left in G2:G5.
To insert check boxes on a worksheet you will need the Developers tab on your Excel ribbon. If you do not see a Developers tab on the Excel ribbon it is easy to add. Read “Adding the Developer Tab to the Excel Ribbon.”
Use the Developer tab to create check boxes that will show or hide portions of your report. In this example the check boxes are in a control area at the top of the report, for your reports it may be more appropriate to put the check boxes on a separate “Report Control” worksheet.
To add a check box, read “Using a Check Box to Dynamically Show or Hide Trend Lines in Excel Charts.”
Each check box also has a macro assigned to it that shows or hides specific rows or objects on the worksheet. You can see the name of each assigned macro by right-clicking a check box and selecting Assign Macro.
Your dynamic report will need to adjust calculations so they calculate only displayed data.
In the example the Jan total is in E35. This total could include NonDigital from E26 and Digital from E33 so the total must adjust to include only hidden values. The formula in E35 that does this is,
The range name slctNonDigital in H4 is TRUE when the Non-Digital check box is selected, so E26 is included in the total. If slctNonDigital is FALSE the check box is not selected so 0 is used in the total. This same process is used for the Digital check box.
The rows are shown or hidden when a check box is clicked. The macro runs VBA code that shows or hides the rows based on the TRUE or FALSE from the check boxes. For example, in the first two lines of the macro below the macro assigned to the Digital check box first checks to see if the check box is selected (TRUE). If it is selected, then on the worksheet tab named “Show Hide Report” the Hidden property for the range “rshDigital” is changed to FALSE. (When the check box is TRUE it means that the rows will show, so the Hidden property is FALSE.)
If the check box is not selected (FALSE), then the Hidden properties are changed to TRUE. (It is hidden.)
If Range("slctDigital") = True Then Worksheets("Show Hide Report").Range("rshDigital").EntireRow.Hidden = False ElseIf Range("slctDigital") = False Then Worksheets("Show Hide Report").Range("rshDigital").EntireRow.Hidden = True End If
Hiding the rows that underlie a chart does not hide the chart. You must hide the rows and chart separately.
In this example, the name of the chart object is “Chart 1”. You can see this name in the Object Selection Pane when you press Alt+F10 in Excel 2013/2016/365.
Showing or hiding a chart is almost the same as showing or hiding rows. The code to make a chart visible is,
Worksheets("Show Hide Report").ChartObjects("Chart 1").Visible = True
To hide a chart, just set the Visible property to FALSE.
ALERT: Note that a ChartObject uses a Visible property, not Hidden, so Visible=True makes more sense than the double negative used with the Hidden property.
You can hide pictures as well as rows, columns, or charts. In this example the Another Global Firm logo, identified as “Picture 2”, is shown with the code,
Worksheets("Show Hide Report").Shapes("Picture 2").Visible = True
Hide the picture by changing its Visible property to False.
ALERT: Note that a Shapes uses a Visible property, not Hidden, so Visible=True makes more sense than the double negative used with the Hidden property.
A couple of other lines of VBA are used in the macros.
Option Explicit forces the declaration of variables. You see the variables being declared with Dim in the top of each macro. You should always declare variables as a way to variables from having the wrong type (numeric, date, etc.) or from mistyping.
Showing and hiding large areas of the screen can cause screen flicker. Prevent this by inserting the line,
Application.ScreenUpdating = False
at the beginning of each macro. This prevents the screen from updating after each change by the macro. When the macro is complete the screen updates once to its new status. You do not need to set ScreenUpdating to True. That is done automatically when the macro is finished.
Showing and hiding rows or columns is great, but what if you want to show or hide a block of content that is in the middle of a report? You aren’t showing or hiding entire rows or columns.
There are two ways that I’ve used to do this. One is to use the same type of check box control for a macro, but the macro changes the font color to white to hide it or to black to show it. Another method you can use with numbers is to create a custom numeric format using the custom format code ;;;. Normally semi-colons separate the three portions (positive;negative;zero) in custom numeric formats. However, if there is nothing between the semi-colons the number disappears.
To learn more about custom numeric formats, check this Microsoft article, Create or delete a custom number format.
This example shows just one way of using VBA with check boxes to show or hide regions and objects in a dynamic Excel report. This example could have been written with much less code, but I’ve used this code because I thought it was most straightforward and easiest to understand for those new to VBA.