Chart Generation. Changing interior color.

D

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("A1:D5"), _
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
 
W

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
 
D

Dan

ward376 said:
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!
 
J

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top