Macro 79: Align a Chart to a Specific Range
Along with adjusting the size of our charts, many of us spend a good bit of time positioning them so
that they align nicely in our dashboards. This macro helps easily snap your charts to defined ranges,
getting perfect positioning every time.
How it works
Every chart has four properties that dictate its size and position. These properties are Width,
Height, Top, and Left. Interestingly enough, every Range object has these same properties.
So if you set a chart’s Width, Height, Top, and Left properties to match that of a particular
range, the chart essentially snaps to that range.
The idea is that after you have decided how you want your dashboard to be laid out, you take note of
the ranges that encompass each area of your dashboard. You then use those ranges in this macro to
snap each chart to the appropriate range. In this example, we adjust four charts so that their Width,
Height, Top, and Left properties match a given range.
Note that we are identifying each chart with a name. Charts are, by default, named “Chart” and the order
number they were added (Chart 1, Chart 2, Chart 3, and so on). You can see what each of your charts is
named by clicking any chart, and then going up to the Ribbon and selecting Format➜Selection Pane. This
activates a task pane (seen here in Figure 7-1) that lists all the objects on your sheet with their names.
Figure 7-1: The Selection Pane allows you to see all of your chart objects and their respective names.
You can use it to get the appropriate chart names for your version of this macro.
Sub Macro79()
Dim SnapRange As Range
SetSnapRange = ActiveSheet.Range(“B6:G19”)
With ActiveSheet.ChartObjects(“Chart 1”)
.Height = SnapRange.Height
.Width = SnapRange.Width
.Top = SnapRange.Top
.Left = SnapRange.Left
End With
SetSnapRange = ActiveSheet.Range(“B21:G34”)
With ActiveSheet.ChartObjects(“Chart 2”)
.Height = SnapRange.Height
.Width = SnapRange.Width
.Top = SnapRange.Top
.Left = SnapRange.Left
End With
SetSnapRange = ActiveSheet.Range(“I6:Q19”)
With ActiveSheet.ChartObjects(“Chart 3”)
.Height = SnapRange.Height
.Width = SnapRange.Width
.Top = SnapRange.Top
.Left = SnapRange.Left
End With
SetSnapRange = ActiveSheet.Range(“I21:Q34”)
With ActiveSheet.ChartObjects(“Chart 4”)
.Height = SnapRange.Height
.Width = SnapRange.Width
.Top = SnapRange.Top
.Left = SnapRange.Left
End With
End Sub
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code into the newly created Module.
