Whilst building a Graphical Dashboard for a client I discovered that the Excel 2007 macro recorder doesn’t record actions that involve placing and manipulating the drawing tools/shapes.

Dashboard – the word means different things to different people but I was recently asked by a customer to “build them a Dasboard in Excel”. The requirements were fairly vague but included:

  • A way to graphically display different sets of summarised data.
  • The ability to switch from one graph to another at the click of a button.
  • The ability to select the most appropriate chart for the selected data.
  • Drop-downs so that they could choose whether to display the data “by country” or “by month”.
  • Drop-downs so that they could select which country and which month to display.
  • On the column chart, they wanted to have a straight horizontal line at 95% to indicate the “Target”.

It was with this last requirement that I hit a brick wall. Rather than create a “mixed type” chart with a second data series fixed at 95%, I thought that the easiest way was to use Excel’s drawing tools to draw a line and then change it’s colour and thickness. This should take me 2 minutes with the Excel macro recorder.

Oh how wrong was I!

I recorded the process and when I looked at the VBA module..horror of horrors…there was no code. So I recorded it again…and again. “Something’s not right here” I thought, so off to Google I went.

It turns out that the Excel 2007 macro recorder doesn’t record actions that involve placing and manipulating the drawing tools/shapes.

However, Microsoft fixed this in Excel 2010. So I opened the file in Excel 2010, turned the macro recorder on and hey presto…I have a working macro. For those interested in the code (and feel free to copy and paste this into an Excel 2007 module)…

ActiveSheet.Shapes.AddConnector(msoConnectorStraight, 280, 69.75, 812, 69.75).Select
With Selection.ShapeRange.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
End With

With Selection.ShapeRange.Line
.Visible = msoTrue
.Weight = 2.25
End With