Chart Generation. Changing interior color.

[Dan]

Here is my question.
I was able to adjust code according to my need and generate chart with data
in Sheet1. But, as it makes chart, I need to have white background instead of
gray. That requires to write code that converts gray area into white. But I
can't place it in the middle of code. It doesn't work there, or in a slightly
different way written it takes a while to change while running the code. Like
gray color would be for 2 or 3 seconds and then switch to white. Not very
fancy at all.

I had to place it at the very end of macro. Anyone, who has ideas how to
solve, please let me know. Code is below.

Sub AddChartSheet()
Dim chtChart As Chart
On Error Resume Next
'Delete chart if there is any
Application.DisplayAlerts = False
Charts("Tool Sales2").Delete
'Create a new chart.
Set chtChart = Charts.Add
With chtChart
.Name = "Tool Sales2"
.ChartType = xlLineMarkers
'Link to the source data range
.SetSourceData Source:=Sheets("Sheet1").Range("A15"), _
PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "=Sheet1!R1C2"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Sales"
End With
chtChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With
End Sub

 

[ward376]

Dan - did you try turning off screen updating until after the chart's
created?

application.screenupdating = false
'make chart
application.screenupdating = true

Cliff Edwards

 

[Dan]

Dan - did you try turning off screen updating until after the chart's
created?

application.screenupdating = false
'make chart
application.screenupdating = true

Cliff Edwards
WOW! That is a very good trick! Thanks a lot Ed!

 

[Jon Peltier]

Also, you can streamline the code by, for example, changing this

chtChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With

to this

With chtChart.PlotArea
With .Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
With .Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With
End With

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______



WOW! That is a very good trick! Thanks a lot Ed!