format mutiple charts via VBA

[Access Beginner]

Hi All,

I have 23 Charts on a sheet which I place into a MS Word Doc. ATM all charts have borders, which is what one of my Managers like, however another Manger likes these charts without borders. I tried to record a macro so I could have this done by VBA (excel 2007) but it did not record the formatting. Can someone please write a code to remove a border and and then add a border back in specifying colour, border styles (width etc). Say just for Chart 38, I can the just repeat this. But here are all the chart numbers in case you need them: 38,20,27,5,11,21,13,6,9,19,10,7,28,29,30,31,32,12,33,34,37,35 & 36.

Cheers

 

[pgc01]

Hi

This is an example that changes the border to vbRed and weight 4 in the charts "Chart 1", "Chart 3" and "Chart 4" in worksheet "Sheet1".

Sub ChangeChartBorder()
Dim chtObjs As ChartObjects
Dim vIndices As Variant, vIndex As Variant

Set chtObjs = Worksheets("Sheet1").ChartObjects

vIndices = Array(1, 3, 4)

For Each vIndex In vIndices
    With chtObjs("Chart " & vIndex).Border
       .Color = vbRed
       .Weight = 4
    End With
Next vIndex

End Sub

HTH

 

[Access Beginner]

Hi

This is an example that changes the border to vbRed and weight 4 in the charts "Chart 1", "Chart 3" and "Chart 4" in worksheet "Sheet1".

Sub ChangeChartBorder()
Dim chtObjs As ChartObjects
Dim vIndices As Variant, vIndex As Variant

Set chtObjs = Worksheets("Sheet1").ChartObjects

vIndices = Array(1, 3, 4)

For Each vIndex In vIndices
    With chtObjs("Chart " & vIndex).Border
       .Color = vbRed
       .Weight = 4
    End With
Next vIndex

End Sub

HTH

Thanks for that I will test this out tomorrow at work. Cheers

 

[pgc01]

OK. Please don't forget to post back the result of your test.

 

[Access Beginner]

OK. Please don't forget to post back the result of your test.

Works perfectly. thanks for your help

Cheers
Haydn

 

[pgc01]

You're welcome. Thanks for the feedback.