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.