Code For Titling All Charts In Workbook

marshman99ca · Jun 9th 2007

I am trying to write some code to make all 24 charts (2 per page and 12 pages) in a workbook have the title of the workbook, and I am obviously doing something wrong. Any ideas are greatly appreciated.

Here is the code.

Code

Sub Cyclecharts()
    Dim i As Integer
    Dim ActChart As String
    
    On Error Resume Next
    For i = 1 To ActiveSheet.ChartObjects.Count
        Range("A1").Select
        ActChart = ActiveSheet.ChartObjects(i).Name
        ActiveSheet.ChartObjects(i).Activate
        ActiveChart.ChartTitle.Select
        With Selection.Font
        .Size = 10
        ChartTitle.Characters.Text = Workbook.Name
        End With
    Next i
End Sub

Display More

Any assistance is greatly appreciated.

JimFuller1 · Jun 10th 2007

Re: Code For Titling All Charts In Workbook

Try this:

Code

.ChartTitle.Characters.Text = ActiveWorkbook.Name

Jim

shg · Jun 10th 2007

Re: Code For Titling All Charts In Workbook

This worked for me. I tried briefly to do it without activating the chart but was unsuccessful. (Andy would know.)

Code

Sub CycleCharts()
    Dim i As Integer
     
    For i = 1 To ActiveSheet.ChartObjects.Count
        ActiveSheet.ChartObjects(i).Activate
        With ActiveChart
            If Not .HasTitle Then .HasTitle = True
            .ChartTitle.Font.Size = 10
            .ChartTitle.Characters.Text = ThisWorkbook.Name
        End With
    Next i
End Sub

Display More

**Reafidy** · Jun 10th 2007

Re: Code For Titling All Charts In Workbook

Quote from SHG

I tried briefly to do it without activating the chart but was unsuccessful

Code

Sub CycleCharts()
    Dim i As Integer
     
    For i = 1 To ActiveSheet.ChartObjects.Count
        With ActiveSheet.ChartObjects(i).Chart
            If Not .HasTitle Then .HasTitle = True
            .ChartTitle.Font.Size = 10
            .ChartTitle.Characters.Text = ThisWorkbook.Name
        End With
    Next i
End Sub

Display More

shg · Jun 10th 2007

Re: Code For Titling All Charts In Workbook

Quote from shg

I tried briefly to do it without activating the chart but was unsuccessful. (Andy would know.)

Edit: So would Reafidy!

Code For Titling All Charts In Workbook

Participate now!

Share