Thread: How change Chart title at one go.

Hi,

I have several charts in excel. The title of each chart is different but one thing is common that is the date mentioned on these chart's title. The date is one weeks range (08-01-08 to 08-06-08). Everytime I have to manually change the date on the chart title. Can someone tell me how to change the date range at one go for all the charts?

Can you post an example to make your question clearer?

I have around 30 charts with date range on them.

Example:-
Chart 1 with the title Qty sold for the period 08-01-08 to 08-06-08
Chart 2 Qty Purchased for the period 08-01-08 to 08-06-08
Chart 3 Amt received for the period 08-01-08 to 08-06-08
so and so forth.

Every week I change only the date of the chart title. It takes lot of time to change these date.

Try the following macro

Code:

Sub RenameCharts()
StartDate = Application.InputBox("Enter start date", , , , , , , 2)
EndDate = Application.InputBox("Enter end date", , , , , , , 2)
For N = 1 To ActiveWorkbook.Charts.Count
    Select Case N
        Case Is = 1
            ActiveWorkbook.Charts(N).ChartTitle.Text = "Qty sold for the period " & StartDate & " to " & EndDate
        Case Is = 2
            ActiveWorkbook.Charts(N).ChartTitle.Text = "Purchased for the period " & StartDate & " to " & EndDate
        Case Is = 3
            ActiveWorkbook.Charts(N).ChartTitle.Text = "Amt received for the period " & StartDate & " to " & EndDate
    End Select
Next N
End Sub

You will need to add further pairs of lines to the three examples before the 'End Select' line.

As long as your title does not contain individual character formatting you could place the 3 titles in cells and link the chart titles to those cells.
The all that is required is updating of the 1 cell that contains the date range.