VBA Excel to set chart back ground to no fill

Hi Experts

I have some code that sets the background of an active chart so that the gridlines can not be seen, code works fine, but the problem is that the background appears to be set by the shape control not the char control t, so if two charts are named the same then the back ground will only set on one chart, is there a better way to set the active shape as the current chart selected regardless of name?

ps. I know I can loop throught the number of shapes and set the shape based upon the chart name.

 
ActiveChart.PlotArea.Format.Fill.Visible = msoFalse
 ActiveSheet.Shapes(LTrim(Replace(ActiveChart.Name, ActiveSheet.Name, ""))).Fill.Visible = _
 msoFalse

Open in new window

MrDavidThornAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
byundtConnect With a Mentor Commented:
This tweak to the original code sets the visibility of the Fill property to False for both the ChartArea and PlotArea.

Sub ChartBackgrounds()
Dim co As ChartObject
For Each co In ActiveSheet.ChartObjects
    co.Chart.ChartArea.Format.Fill.Visible = msoFalse
    co.Chart.PlotArea.Format.Fill.Visible = msoFalse
Next
End Sub

Open in new window

0
 
byundtCommented:
You might try looping through the ChartObjects collection to avoid conflicts with duplicate names. The code below sets the background color to white.

Sub ChartBackgrounds()
Dim co As ChartObject
For Each co In ActiveSheet.ChartObjects
    co.Chart.ChartArea.Format.Fill.ForeColor.RGB = RGB(255, 255, 255)
Next
End Sub

Open in new window

0
All Courses

From novice to tech pro — start learning today.