Thanks for the thoughts Jon.
The cells are definitely blank, and I've managed to kludge to a point
where the errors are suppressed during generating it (the Excel workbook
is created from an external application).
Then I hit a problem where if I modified the chart in any way at all
(including selecting a chart title), I got the same "negative values"
error and Excel crashed. This has to be a bug in Excel, it's inexcusable
:-)
But I also noticed that if I changed a value in the underlying dataset I
got the same error, and then Excel was happy. It even let me move things
in the chart without crashing.
So the best solution to date is to use the Workbook_Open function to
change a value, then change it back. This creates the annoying message
when the workbook is first opened but does prevent Excel from crashing.
I'll see if the NA approach does any better, although since it's failing
to read blanks I'm wondering if we're fundamentally up against Excel
bugs rather than code quirks. Another range is easy enough to produce,
I'll just make it white on white and tuck it out of the way.
In message , Jon Peltier
writes
I was surprised that 2003 didn't give me thee message at all, even in manual
mode. I seem to recall being ticked at getting the message a hundred times
when one plot had lots of negative numbers.
Are the cells really blank, or do they contain formulas? if they are
formulas, maybe returning some value besides a simulated blank might help.
For other purposes, we use NA() to condition the result of a formula for
being left out of a chart. If the cells are or may be blank, link another
range to them, and likewise condition the blanks, then plot from this second
range. And before you say anything about having two ranges, think how nice
it would be if this stopped the messages.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______
"Newsx" wrote in message
...
Thanks for that Jon, I tried the application.displayalerts trick and it
kind of worked, much like you mentioned.
The trouble is even if I leave displayalerts off all the time (which
causes its own issues) if I then do anything to the chart at all
(including moving an axis title etc) I get the error.
Since this is for a user base that isn't very technically aware I can't
really afford to have strange messages like that popping up :-(
It's odd in that Excel 2003 handles everything just fine, but Excel 2007
just doesn't want to deal with blank values.
In message , Jon Peltier
writes
In a very simple line chart with one series having one negative value,
when
I manually set the scale to logarithmic, I got the error the first time,
but
not subsequent times. It's as if Excel remembered it had already bothered
me
about this. Despite the warning, the axis was changed. When I created a
new
chart (which I had not been warned about) and used the VBA code, I got the
error; after clearing the error, the axis was changed. However, you want
to
avoid the error. I tried EnableEvents:
Sub LogScale()
Application.DisplayAlerts = False
ActiveChart.Axes(xlValue).ScaleType = xlLogarithmic
Application.DisplayAlerts = True
End Sub
but it did not prevent the message. I don't know what to suggest, but I'm
waiting to see what others may have to say.
I was using Excel 2007 SP1, as if it matters.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______
"Newsx" wrote in message
.. .
I'm trying to put together a piece of code in Excel, to be called by an
external application to regenerate graphs.
The offending code is this:
ActiveChart.PlotVisibleOnly = True
ActiveChart.DisplayBlanksAs = xlNotPlotted
ActiveChart.Axes(xlValue).ScaleType = xlLogarithmic
The process fails when trying to set the scaletype to logarithmic,
claiming that negative or zero values cannot be plotted.
There are no negative or zero values in the data series but there are a
number of blank cells. This works perfectly in Excel 2003 but Excel 2007
throws errors.
This is really doing my head in, so any pointers would be much
appreciated!
--
Confidence is what you have when you don't understand the situation
--
================================================== ====================
To reply by email remove the word master from before the @
Any email sent to reply-to address will be automatically rejected
Any email sent may be quoted in full or in part in relevant newsgroups
================================================== ====================
Confidence is what you have when you don't understand the situation
--
================================================== ====================
To reply by email remove the word master from before the @
Any email sent to reply-to address will be automatically rejected
Any email sent may be quoted in full or in part in relevant newsgroups
================================================== ====================
Confidence is what you have when you don't understand the situation