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.