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
