R
roybrew
I am in the process of implementing some dynamic charting and have run
into some problems that I cannot see to get past. I was hoping someone
else had seen this issue before. I have distilled my problem down to a
simple example, though. Given the following sample sheet:
Jan Feb Mar
Gross 1 10 100
Cogs 2 20 200
Exp. 3 30 300
Misc 4 40 400
If I chart that sheet range and choose to PlotBy (Series in) rows
(default), thus putting the row titles on the legend. The chart type
doesn't matter too much, but for the sake of this exercise, I chose
Clusterd Column. Everything at this point is fine and the chart looks
as it should. I then register this chart with a ChartEvent class so
that I can "see" the chart events. No problem there.
Now, in order to facilitate dynamic charting, I have code that does,
say, a delete of some of the rows in the above sheet (Cogs & Exp).
When I do that, I also "kick" the chart and reset the data source range
with the SetSourceData method and nothing else. The PlotBy parameter
to that method is supposed to be Optional. However, the sequence of
events hoses things.
When the Delete of the Cogs and Exp rows happens, the Legend containing
the row titles changes to #REF errors for the deleted rows, which I
would expect. The Chart_Calculate event fires as a result of the
delete operation. If I look at the PlotBy property when the
Chart_Calculate event fires it is set to some bogus value (2042).
Legal values are 1=Rows, 2=Cols.
I then "kick" the chart, resetting the data source range (and only the
range, not the PlotBy property). This again fires the Chart_Calculate
event. The end result is that Excel swaps the titles that appear in
the legend on the sheet. The column titles suddenly appear in the
legend and the row titles now appear on the x-axis of the graph.
Looking at the PlotBy property indicates that it is now magically set
to columns (2). I never explicitly set it to columns, though. And
although in this simple example I could pre-read the PlotBy value and
just reset it when I change the data source range, in my real code
where we have implemented some dynamic spreadsheets (and are attempting
dynamic charting) we do all of the "maintenance" on the sheet range
first and then when the changes have been made to the range we then go
update all of the charts that point to that range. But by the time we
get to do that on an event that we can trap (i.e. Chart_Calculate) it
is too late. The PlotBy property is already corrupted with a 2042 and
we can never recover the pre-delete value of this paramenter without
keeping it stored in some global data structure. While that is
certainly an option, it is not preferred. My main question is:
Why is Excel automatically deciding that the PlotBy value should change
from Rows to Cols?
Thanks in advance for any info on this issue.
roy
(e-mail address removed)
into some problems that I cannot see to get past. I was hoping someone
else had seen this issue before. I have distilled my problem down to a
simple example, though. Given the following sample sheet:
Jan Feb Mar
Gross 1 10 100
Cogs 2 20 200
Exp. 3 30 300
Misc 4 40 400
If I chart that sheet range and choose to PlotBy (Series in) rows
(default), thus putting the row titles on the legend. The chart type
doesn't matter too much, but for the sake of this exercise, I chose
Clusterd Column. Everything at this point is fine and the chart looks
as it should. I then register this chart with a ChartEvent class so
that I can "see" the chart events. No problem there.
Now, in order to facilitate dynamic charting, I have code that does,
say, a delete of some of the rows in the above sheet (Cogs & Exp).
When I do that, I also "kick" the chart and reset the data source range
with the SetSourceData method and nothing else. The PlotBy parameter
to that method is supposed to be Optional. However, the sequence of
events hoses things.
When the Delete of the Cogs and Exp rows happens, the Legend containing
the row titles changes to #REF errors for the deleted rows, which I
would expect. The Chart_Calculate event fires as a result of the
delete operation. If I look at the PlotBy property when the
Chart_Calculate event fires it is set to some bogus value (2042).
Legal values are 1=Rows, 2=Cols.
I then "kick" the chart, resetting the data source range (and only the
range, not the PlotBy property). This again fires the Chart_Calculate
event. The end result is that Excel swaps the titles that appear in
the legend on the sheet. The column titles suddenly appear in the
legend and the row titles now appear on the x-axis of the graph.
Looking at the PlotBy property indicates that it is now magically set
to columns (2). I never explicitly set it to columns, though. And
although in this simple example I could pre-read the PlotBy value and
just reset it when I change the data source range, in my real code
where we have implemented some dynamic spreadsheets (and are attempting
dynamic charting) we do all of the "maintenance" on the sheet range
first and then when the changes have been made to the range we then go
update all of the charts that point to that range. But by the time we
get to do that on an event that we can trap (i.e. Chart_Calculate) it
is too late. The PlotBy property is already corrupted with a 2042 and
we can never recover the pre-delete value of this paramenter without
keeping it stored in some global data structure. While that is
certainly an option, it is not preferred. My main question is:
Why is Excel automatically deciding that the PlotBy value should change
from Rows to Cols?
Thanks in advance for any info on this issue.
roy
(e-mail address removed)