Wednesday, May 25, 2011

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.

Monday, May 16, 2011

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.

Thursday, May 12, 2011

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.

Tuesday, April 19, 2011

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

In the next 3 posts I will show you a neat way to display your data with charts. By using drill-down menus we can show multiple data sets - all related in some way - in the same chart, with one data set showing at a time. Download the example file here and try it out yourself.

What you can see in "Dashboard" tab is one chart, with a drill-down button as shown below.



Clink on the button, and a drill-down menu will appear.


This drill-down menu provides the user with multiple options, from showing the sales data on the national level to showing same data on the district level. The chart will redraw automatically each time a different option item is clicked. Furthermore, headers in Range C1 as well as the textbox right above the chart will also change accordingly.

I see at least two big advantages to utilize such a drill-down menu:
1), The data is being presented in a way that is highly organized. By designing your own drill-down menus you can group related data sets together, making your data logically structured and easy to follow.
2), By using one chart area for multiple data sets you also save space, and your report will look so much more clean and usable.

I will only show you the key sections of Macro in my posts and you can always refer to the Macro in its entirety in the example file you just downloaded.


Friday, April 1, 2011

Print Reports into PDF Files

Let's do something fun: print all our territory-level call panel worksheets into PDF files. It has become more and more popular among biotech managers to distribute their reports in PDF format to avoid any formatting discrepancies as well as potential data tempering. This is suitable for any reports that do not require user-interaction, such as IC Payout Scorecard, Goal Attainment Scorecard, etc.

Before we can programatically do this, we need to install a free software called "PDFCreator" on the report creator's machine (not necessary on end-users' computers). The download link is here. After proper installation, go to "VBA Editor" in Excel, choose "Tools" -> "References", and select "PDFCreator", click "OK". This allows VBA access to all the properties and methods of PDFCreator library.

The code to print all 99 territory-level workbooks into PDF files is shown below. Note that for demonstration purpose we will only print out the first two pages for each file. You can download the example file here.


Change the "mkdPDFName" and "mkdPDFPath" variables to your specifications. If you would like to print out all pages (not just the first two), remove "From" and "To" parameters in the above "ActiveSheet.PrintOut" line.

Combining what we have done in this post and the past one, we can easily print out Excel worksheets as PDF files, then immediately email them to various recipients via Outlook.

Monday, March 28, 2011

Email Reports to Multiple Recipients using VBA

In this post I will show you how to email reports (as attachments) to various recipients via Outlook using VBA. It is a less commonly-used function of VBA to control other Windows processes beyond Excel, but no less powerful. Once we have the Macro in place, we will be able to:

1),Send territory-level reports to corresponding territories. Each territory will only receive one email with one report attached.
2), Send each email with customized Subject line and body message showing, for example, territory name.

Before I show the code, we need email addresses for all territories as shown in the following snapshot:


Add the following Macro to your workbook. Download the example here.


The code above calls an Outlook object and then create a new mail item. Once that's done, we can change the properties of that item (such as .To, .Subject, .Body) to send an email to a specific address with specific messages. Finally, .Attachments.Add method is used to attach the currently opened workbook with the outgoing email.

Several things to keep in mind:
1), Outlook must be open and running when you execute the Macro.
2), Outlook may give a security warning every time an email is sent. Readjust the security settings in your Outlook to disable that message.
3), To add a line break in the body of the message, use vbCrLf as part of the string.

Tuesday, March 22, 2011

Finalize Call Panel Report - Add Filtering, Remove Opt-out MD Info, Protect Sheets, and Some More

Let's add some final features to our national level call panel file and call it a day.

One is to add the function for the end-user to filter records by specialty. For example if the user chooses "Spec3", then only those MDs with a specialty of "Spec3" would show up. All others will be hidden. Not the most fancy, but many times it would prove to be useful for people not familiar with using Auto-Filter.

To do this, we create another drop-down list which allows the user to choose one of the six specialties, and add the following code. You can download the workbook here.


In the above code we use "RowHeight" property to display/hide rows. Here we set "RowHeight" to 13 (default row height on my computer) to display the rows; and to 0 to hide them. If necessary, this property can also be read first to determine what is the default row height on the end-user's machine.

Let's move on to another topic: Opt-out physicians - doctors who do not wish to share their information (can be sales data, can be demographic info) with any sales reps. Biotech managers are legally required to remove such information in any reports sent to the sales force. In our example, let's assume that we have a list of 400 physicians who are opt-out doctors. The only information we are allowed to show is their MD#, Region and Territory, Specialty and Name. All other fields need to be blocked. The VBA code is shown below:

We can see from the above code that this 400 Opt-Out physician list is on sheet "DropDownList" and occupies the range "G1:G400". If you do not wish to show this list to the sales force, you can choose to hide the sheet (see below), or clear the list by using .Range().ClearContents, or have the list resided on a different Excel file.

Finally, before we send out the file to everybody, we would like to hide the sheets "Ter List" and "DropDownList" by using:

.Sheets(1).Visible = False

If you also prefer to hide column and row headings ("A", "B", "C", etc.) you can use:

ActiveWindow.DisplayHeadings = False

Some people prefer to protect all sheets in the workbook with a password so that no (unintentional) changes can be made to any contents within the sheets. To do this, use:

.Sheets(1).Protect(Password)

Word of caution. After you protect a sheet, if a Macro needs to write to/change the sheet, an error will be generated. To avoid this, use:

.Sheets(1).UnProtect(Password)

to temporarily unprotect the sheet, do your thing, then protect it again.

Finally, to protect the VBA code itself with a password (after all, any person with some VBA knowledge can easily go through your code and retrieve the password contained in the above Protect and UnProtect methods), go to VBA Editor, right click on the VBA Project, and choose "VBAProject Properties...".