Format graphs across workbook

[Big Bruiser]

I have a workbook with several tabs and graphs on most tabs. The graphs contain titles and borders for presenation within Excel. However, when I copy and paste the graphs into PowerPoint, I prefer the graphs to not have a title and no borders. I would like to save myself the time of making this adjustment every time I create a PowerPoint. Is there a way to write a macro to reformat all of the graphs in the workbook? Ideally it would just hide the title, but it could also delete it. The borders just need to be reformatted to "no line."

I would appreciate any help!

Thanks!

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim ws As Worksheet
    Dim ch As ChartObject
    For Each ws In ActiveWorkbook.Worksheets
        For Each ch In ws.ChartObjects
            With ch.Chart
                .HasTitle = False
                .ChartArea.Border.LineStyle = 0
            End With
        Next ch
    Next ws
End Sub

 

[Big Bruiser]

Wow works perfectly!! Thanks for the quick, accurate response!!

 

[Big Bruiser]

Only additional question I have is can the title be hidden or does it have to be deleted? When I run the macro it deletes the title, but if I want to toggle back and forth between showing and hiding the title, the original title is gone and it simply says "Chart Title." Is there a way to keep the old title?

My current code is

Private Sub CommandButton5_Click()
Dim ws As Worksheet
Dim ch As ChartObject

If CommandButton5.Caption = "Remove Titles" Then
For Each ws In ActiveWorkbook.Worksheets
For Each ch In ws.ChartObjects
With ch.Chart
.HasTitle = False
.ChartArea.Border.LineStyle = 0
End With
Next ch
Next ws
CommandButton5.Caption = "Show Titles"
ElseIf CommandButton5.Caption = "Show Titles" Then
For Each ws In ActiveWorkbook.Worksheets
For Each ch In ws.ChartObjects
With ch.Chart
.HasTitle = True
.ChartArea.Border.LineStyle = 1
End With
Next ch
Next ws
CommandButton5.Caption = "Remove Titles"
End If
End Sub

 

[Andrew Poulsom]

Setting HasTitle to False effectively deletes the Title. Maybe you could set its Font and Border to the same colour as the ChartArea Fill.

 

[Big Bruiser]

Here is my final solution. thanks!

Private Sub CommandButton5_Click()
    Dim ws As Worksheet
    Dim ch As ChartObject
    
    If CommandButton5.Caption = "Remove Titles" Then
    For Each ws In ActiveWorkbook.Worksheets
        For Each ch In ws.ChartObjects
            With ch.Chart
                If .HasTitle = True Then
                .ChartTitle.Font.Color = RGB(255, 255, 255)
                .ChartArea.Border.LineStyle = 0
                End If
            End With
        Next ch
    Next ws
        CommandButton5.Caption = "Show Titles"

ElseIf CommandButton5.Caption = "Show Titles" Then

    For Each ws In ActiveWorkbook.Worksheets
        For Each ch In ws.ChartObjects
            With ch.Chart
                If .HasTitle = True Then
                .ChartTitle.Font.Color = RGB(0, 0, 0)
                .ChartArea.Border.LineStyle = 1
                End If
            End With
        Next ch
    Next ws
        CommandButton5.Caption = "Remove Titles"
End If

End Sub