Help with graphs

[Guest]

Hi. In a worksheet I have some code which automatically updates the Gridlines
each time new source data is updated. It has been working fine however for
some reason now, it updates whilst going through the code, but when a cell is
chosen outside the graph eg cell A8, it resorts back to the original scale.
Code as follows:

With ActiveChart.Axes(xlValue)
.MinimumScale = 0
.MaximumScale = 40000000
.MinorUnit = 200000
.MajorUnit = 5000000
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
Range("A8").Select

Can anyone see what is going wrong here and why the gridlines don't stay on
the scale above?

Cheers.

 

[Jon Peltier]

Where is the code? In the worksheet module? This piece of code doesn't
respond to any changing values, it sets the major and minor unit (which set
the gridline spacing) to constant values.

- Jon

 

[Guest]

Yes, this is just an excerpt from the full code within the worksheet module.
It does set the major and minor unit, however when the macro has finished the
gridlines revert back to the previous scale. When I step through the code
line by line, I can actually see the gridlines changing to what the code
specifies, however once it steps out of the graph (ie to cell A8) the scale
changes back to the old scale....which I cannot explain.

 

[Jon Peltier]

I cannot explain it either. Is there more code in a SelectionChange event
procedure which may affect the chart?

- Jon

 

[Guest]

Perhaps it is just "one of those things". I actually have 2 graphs which
update at the same time (well, one after eachother that is). One works fine
but the other reverts to the original data each time once the code comes out
of the graph. The only way to change the scale is to manually click in and do
it. Those changes then become the scale it keeps reverting back to. So,
strangely enough graph 1 is dynamic but graph 2 is not. Funny thing is it was
working fine one day and then not the next. It's quite simple code and I do
not have a SelectionChange event. It may be a case of re-writing the code
from scratch.

 

[Jon Peltier]

There must be more code that you didn't post. Maybe it would help to see it
all.

- Jon

 

[Guest]

ok - here is the code. You will see 2 products with different data. SUMIF's
are used to populate data range for graphs which are working fine. You will
notice two charts with numbers to change the scale. Chart 32 works fine and
changes depending on which product is selected. Chart 60, however is the one
which changes during the macro to the correct scale, but once it steps to
Range("A8").Select, the scale reverts back to the original scale. This is the
part I don't understand...

Sub Group_Selector_and_Line_Graph_Update()

If Range("B6").Value = "Product 1" Then

Range("A8").Select
ActiveSheet.Outline.ShowLevels RowLevels:=2
Rows("47:58").Select
On Error Resume Next
Selection.Rows.Ungroup
Rows("64:75").Select
On Error Resume Next
Selection.Rows.Ungroup
Rows("81:92").Select
On Error Resume Next
Selection.Rows.Ungroup

Application.Goto Reference:="P1"
Selection.Copy
Sheets("Portfolio Graph").Select
Range("B47").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Rows("52:58").Select
On Error Resume Next
Selection.Rows.Group
Rows("69:75").Select
On Error Resume Next
Selection.Rows.Group
Rows("86:92").Select
On Error Resume Next
Selection.Rows.Group
Range("A8").Select
ActiveSheet.Outline.ShowLevels RowLevels:=1

ActiveSheet.ChartObjects("Chart 32").Activate
ActiveChart.Axes(xlValue).MajorGridlines.Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 2000000
.MaximumScale = 3000000
.MinorUnit = 40000
.MajorUnit = 200000
.Crosses = xlCustom
.CrossesAt = 2000000
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
Range("A8").Select

ActiveSheet.ChartObjects("Chart 60").Activate
ActiveChart.Axes(xlValue).MajorGridlines.Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0
.MaximumScale = 10000000
.MinorUnit = 200000
.MajorUnit = 2000000
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
Range("A8").Select

ElseIf Range("B6").Value = "Product 2" Then

ActiveSheet.Outline.ShowLevels RowLevels:=2
Rows("47:58").Select
On Error Resume Next
Selection.Rows.Ungroup
Rows("64:75").Select
On Error Resume Next
Selection.Rows.Ungroup
Rows("81:92").Select
On Error Resume Next
Selection.Rows.Ungroup

Application.Goto Reference:="P2"
Selection.Copy
Sheets("Portfolio Graph").Select
Range("B47").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Rows("52:58").Select
On Error Resume Next
Selection.Rows.Group
Rows("69:75").Select
On Error Resume Next
Selection.Rows.Group
Rows("86:92").Select
On Error Resume Next
Selection.Rows.Group
Range("A9").Select
ActiveSheet.Outline.ShowLevels RowLevels:=1

ActiveSheet.ChartObjects("Chart 32").Activate
ActiveChart.Axes(xlValue).MajorGridlines.Select

With ActiveChart.Axes(xlValue)
.MinimumScale = 9000000
.MaximumScale = 13500000
.MinorUnitIsAuto = True
.MajorUnit = 1000000
.Crosses = xlCustom
.CrossesAt = 9000000
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
Range("A8").Select

ActiveSheet.ChartObjects("Chart 60").Activate
ActiveChart.Axes(xlValue).MajorGridlines.Select

With ActiveChart.Axes(xlValue)
.MinimumScale = 0
.MaximumScale = 40000000
.MinorUnit = 200000
.MajorUnit = 5000000
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
Range("A8").Select
nd If

End Sub

 

[Jon Peltier]

Sorry, the additional code hasn't shed any additional light on the issue.

- Jon

 

[Guest]

Jon hope you can help. I have data which feeds in to a simple chart. data is
added each day and i want the chart to update with the new data. The problem
i have is if i extend the date range to include future values i add the graph
looks messy. This is because the graph plots cumm totals so for eg the
current cumm value up to 16th Mar is 2544 if i extend the range up to say 31
dec then i have 2544 values upto dec 31st waiting for new values to be added.
So currently i have to manually change the date range to include the current
last row which is a bit clumsy. Do you have a simple solution to this
thank you

 

[Jon Peltier]

Dynamic Charts:

http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html
http://peltiertech.com/Excel/Charts/Dynamics.html

- Jon