Chart axes setting problem

[bobslay]

Hi,

I'm having problem with a very simple piece of code that I've used in a spreadsheet for a couple of years. I had to upgrade to Excel 2007 and now the code crashes each time.

The code is:

Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlCategory)
.MaximumScale = ActiveSheet.Range("b42").Value
.MinimumScale = ActiveSheet.Range("a42").Value
End With
End Sub

and each time it runs now it gives the message:

Method 'MaximumScale' of object 'Axis' failed.

I have tried replacing the line with:

.MaximumScale = 100

and the same error occurs.

Can anyone please help?

Thanks

[tstav]

Could it be that you actually mean to set the axes(xlValue) and not the axes(xlCategory)?

I'll rephrase that: It doesn't error for the axes(xlValue). Are you sure you need to set the axes(xlCategory)?

[Bob Phillips]

That code errors out in 2003 also.

[Andy Pope]

Or the chart does not have a numerial category axis.
For example a Line chart with category axis would error but change the axis type to time series and it will work.

[bobslay]

Hi,

Thanks for all of the replies, the code worked in 2003 for 2 years so it is strange that someone suggested it errors in 2003 also.

I changed xlCategory to xlValue but it still errors out in the same way at the same point.

The chart axes type is numeric.

Any other suggestions/ideas? I can sent the entire sheet if that helps

Thanks

[tstav]

Does it still error out if you change it to
With ActiveSheet.ChartObjects(1).Chart.Axes(xlCategory)

[rory]

I think it would be useful to see the workbook in question. Chart code in 2007 can be quirky from what I've seen.