Macro to customise chart axis

**D'Artagnan** · 01-27-2010, 11:31 AM

Hi,

I have been asked to modify some Excel charts to override the autoscaled axis that Excel uses. The request is that the axis should be larger than largest value that it is graphing. For example if I am graphing a series of data points from 1 to 8 then the scale needs to go up to 9 or 10 and also on the other end of the scale it would need to show -1 or -2 depending on what the user wants to show.

I tried writing some VBA code to do this and it appears to work except once the macro is linked to the chart I am unable to modify the chart. By that I mean I can no longer select the columns or axes or do any modification.
The macro just links to two cells in the spreadsheet that specify the max and min values. This is how I would like to create the chart so that the user can changes those cells if he wishes to increase or decrease the scale.

Please Login or Register  to view this content.

Is there a way of defining the max and min values of the axes without running into this problem? Simply going into the Format Axis box won't do as the user is not comfortable making these changes and wants it all done automatically.

I appreciate any help you can give me on this.

Thanks

**Andy Pope** · 01-27-2010, 12:32 PM

If you have assigned the macro to the chart object then clicking the chart will run the macro but not allow you to select chart elements.

If you right click the chart you can choose Edit Chart Object.

Depending on your workbook you could use the change event of the worksheet to update the chart when cells in a known range are changed.

With the use of a class object you can capture the Calculate event and run the macro

**D'Artagnan** · 01-28-2010, 05:21 AM

Hi Andy,

Thanks for that. I don't think telling this guy that he has to right click and choose Edit Chart Object will work as he is not very competent with Excel and doesn't like having to do anything like that.

I have heard about change event of the worksheet before and if I understand correctly it is in the VBA Editor where I am able to bring up the following code and then insert my own:

Please Login or Register  to view this content.

However I am not too sure what sort of code I need for this to work? Will the existing code work if I put it in here? Or will it again prevent me from selecting the chart elements?

Thanks

**Andy Pope** · 01-28-2010, 05:41 AM

This assumes chart data in range O1:O100

Please Login or Register  to view this content.

**jvonnemann** · 02-05-2010, 10:45 AM

My problem is that for a certain kind of chart I don't want to have the axis in the middle of the chart. The scale of the chart must be fixed so that the minimum and maximum values are -1 and +1 for both.
The x-axis and the y-axis then appear in the middle of the chart because they are always at the 0-line. But I want to have the axis to stay fixed at x = -1 and y = -1, although the scale remains as described (-1,+1). How can I do that?

Thanks for helping me and kind regards to the profs.

**Andy Pope** · 02-05-2010, 10:57 AM

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

When starting your new thread it may help to post example workbook of your chart