Programming for Excel Charts

[AstroProg]

The code below gives a Type Mismatch error on the .MinimumScale = startmonth
and the line afterward. This worked in Excel 2003 but gives the error in
Excel 2007. It appears you cannot use a string with the MinimumScale command.

Any ideas how to work around this? startmonth and stopmonth are dates such
as 01/01 and 01/31. Below is the VBA code.

Chart1.Activate
ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.MinimumScaleIsAuto = False
.MaximumScaleIsAuto = False
.MinimumScale = startmonth
.MaximumScale = endmonth

 

[Joel]

I don't have Excel 2007 but I usually solve these problems by using the Macro
Recorder. Start Recording a macro and then make the scale changes manually.
Highlight the axis with the mouse and then double click to bring up scale
properties. Make the change you want and then stop recording.

 

[AstroProg]

A Macro won't work here. The min and max for the x axis of the graph change
each month, so they need to determined by using code to go through a list of
dates.

 

[Joel]

A macro is CODE. There is no difference. Using the macro recorder will help
you to determine the correct syntex. You can then modifiy the recorded macro
as required.

 

[Jon Peltier]

Is startmonth a date, or a string containing a month name? If It's a
nonnumeric and non-date string, it didn't work in 2003 either.

In the Format Axis dialog, Excel 2003 and earlier allowed you to enter min
and max for any axis as numbers, dates, times, whatever you had. Excel 2007
allows a date scale axis to be controlled using dates only (no numbers), and
value axes to be controlled using numbers only (no dates and times). I don't
know if the problem extends to VBA. I doubt it, but you never know.

If this is a value axis and your scale parameters are dates, try converting
them to numbers, using CDbl(startmonth) etc.

- Jon