Spruce Up Your Dashboard Using Charts and Drill-Down Menus - Part 4

In this last post of the series, I will show you how to run Macro automatically when the end-user opens our workbook, and when he/she closes it.

Why do we need to run events when the workbook is being opened? In the current example, we want to make sure that all the main elements of the worksheet - i.e. an embedded chart, a "Drill-Down" button, and a shape serving as the chart title - are properly positioned relative to each other on the end-user's computer.

Why do we need to run events when the workbook is being closed? In the current example, we want to make sure that if a drill-down menu still exists and shows in the environment when the user clicks the "x" button, the menu is deleted and removed from the system.

Here is the code. DO NOT put the code in Modules as we normally do. It should be put in the "ThisWorkBook" section under "Microsoft Excel Objects"."Workbook_Open()" event fires up when the workbook is opened AND the Macro is enabled. "Workbook_BeforeClose()" event fires up when the user clicks on the "x" button (the "Close" button).

As we see, we can position shapes (Left, Top) relative to given cells in the worksheet, giving us the ability to do some precise positioning. For example,
.Shapes("Chart1").Left = .Range("B6").left
positions the left edge of the chart to the left edge of the cell B6. Here we are treating the chart as a shape object rather than a chart object as our only concern here is to position the chart, not to manipulate any chart elements.

Spruce Up Your Dashboard Using Charts and Drill-Down Menus - Part 3

Excel VBA has provided some very useful and powerful features to manipulate data sources as well as the appearance of embedded charts. As a matter of fact almost EVERY single aspect of a chart can be modified by Macros. The example I show here should give you a taste of such functionalities.

First things first, we need a name for the chart we intend to control so that we may effectively reference the chart in our codes. There are two ways to locate and change the name of an embedded chart. Click on the chart, and look in one of the following two places:

Enter a customized name (if you choose) into one of the two textboxes outlined above and press enter. Make sure that each chart has its own unique name.

Now let's take a look at the following codes we used to chart "Monthly Regional Sales" in the given example. Once again, you can download the example file here.

Key grammars of the codes include:

1), Sheets("Dashboard").ChartObjects("Chart1"), is used to reference an embedded chart named "Chart1" in worksheet named "Dashboard". Please note that to reference chart elements such as chart titles, legends and data series, we should use:

Sheets("Dashboard").ChartObjects("Chart1").Chart.ChartTitle

Sheets("Dashboard").ChartObjects("Chart1").Chart.Legend

Sheets("Dashboard").ChartObjects("Chart1").Chart.SeriesCollection

2), Before we redraw the chart we need to delete the current content in the chart - i.e. all data series (circled by red pen) and (potentially) trendlines (circled by blue pen). Now that if there is currently no trendline in the chart, the attempt to delete it would trigger an error message. Use "On Error Resume Next" to ignore all such errors.

3), The line to determine the chart type (i.e. if it's a line chart or a column chart) is underlined by black pen: .Chart.ChartType = xlLine. A good source for all chart types can be found here.

4), Codes circled by Magenta pen detail how to add a data series as part of a line chart, as well as how to customize the look of the data series. Most properties are self-explanatory (for example xValue determines the x-Axis data range, .Border.Color determines the line color, etc). One property, .Points(.Points.Count), references to the last (right-most) data point of the data series. To reference the first data point, use .Points(1). To reference all data points, set up a loop between 1 and .Points.Count.

5), Codes detailing how the axes and legend would appear are pretty easy to understand too. One thing I would like to point out is .Chart.Axes(xlValue) refers to the y-Axis while .Chart.Axes(xlCategory) refers to the x-Axis.

Spruce Up Your Dashboard Using Charts and Drill-Down Menus - Part 2

Continuing from the last post, here is the code that creates a "Pop-up" menu each time the user clicks on the "Drill-Down" button:

The "Drill-Down" button is associated with the Macro show_mkdMenu1(). This Macro does three things:

1), Delete the menu already created (blue underline). This ensures that at any given time no more than one menu exists in the environment. This menu always has the name "mkdMenu1". Note that when the user first opens the workbook there is no menu ever created and hence any attempt to delete a non-existent object will result in an error and break. To avoid this we used "On Error Resume Next" to ignore any such potential errors.

2), Call Macro create_mkdMenu1() to create, but not show, a new menu. See below for the details on how to create a multi-layer menu as I did with the example.

3), Show the menu we just created (red underline).

Now let's dig a little deeper into Macro create_mkdMenu1().

To create a multi-layer menu as shown in the example (there are three items in the first layer labeled with numbers 1, 2 and 3. One more item "MKD1 Monthly District Sales" appears as a sub-menu upon which a second-layer with two more items will open), the key is to add new controls as either:

1), Type:=msoControlButton, which adds a normal menu item. When clicked, another procedure named in "OnAction" property will be executed. As shown in our example, values can be passed to such procedure when called. The "FaceId" property determines what icons will appear in front of the item, i.e. FaceId = 71 will show number "1", etc. There are thousands of FaceIds you can apply to individualize your menu. A good link is here.

2), Type:=msoControlPopup, which adds a sub-menu group. As we can see from our example, there is no "OnAction" or "FaceId" properties associated with this kind of control as it is only used to open a second-layer menu items. To add sub-menu items, repeat the procedures we discussed in step 1) above.

In the next post I will show you how to manipulate charts using VBA.