cellMatrix.net

Numbering Charts in a Worksheet Grid

Many of the models I work with require a series of embedded charts to appear on a single worksheet. Each chart is usually numbered for reference. The numbers usually appear in a worksheet cell directly above the right-side of each embedded chart.

Below is a macro that will quickly add a number for each embedded chart to a cell on the upper right-side of the chart:

Sub NumberCharts()

    Dim i As Integer
    Dim Rng As Range
    Dim ColumnsAcross As Integer
    Dim RowsDown As Integer

    '*************************************************

    'The range where you want the first "Chart 1" to appear

    Set Rng = ActiveSheet.Range("F3")

    '# of columns to the right that you want "Chart 2" to appear

    ColumnsAcross = 5

    '# of rows down that you want "Chart 3" to appear

    RowsDown = 13

    '*************************************************

    i = 0

    Do Until i = ActiveSheet.ChartObjects.Count

        i = i + 1

        Rng.Value = "Chart " & i
        Rng.HorizontalAlignment = xlRight

        i = i + 1

        Rng.Offset(0, ColumnsAcross).Value = "Chart " & i
        Rng.Offset(0, ColumnsAcross).HorizontalAlignment = xlRight

        Set Rng = Rng.Offset(RowsDown, 0)

    Loop

End Sub

The numbers appear on the upper right-side of the chart similar to below: