Changing scale axis

[lmigarci]

Hi all. I would like two change the axis scale of a chart by writing the values into a cell. How could I manage with that? Should I use VBA? I don't have any experience on that but I could try with your help.

Thank you.

[Andy Pope]

VBA is the only way as you can not directly link scale values to cells.

Something like this will do it for the Value axis

Please Login or Register  to view this content.

[lmigarci]

Thx Andy. I'm trying to use this code for the file I sent you a couple of days ago. I haven't used before this VBA and I'm trying to find out the way it works. I do the following steps:

1) Open the visual basic editor.
2) Insert new module
3) Copy and paste this code there:

With ActiveChart.Axes(xlValue)
.MinimumScale = Range("A1")
.MaximumScale = Range("A2")
End With
4) write in the corresponding values so, A1=1000 A2=2000

is that all? Does the chart change automatically??any other modifications?

I would appreciate also if you could tell me where to find a link with some information about VBA for beginners.

Thx

[Andy Pope]

There is a little more to it than that.

You would need to wrap the code in a procedure.
Then select the chart and run the macro

Please Login or Register  to view this content.

Or you could add code to the worksheet change event so the code is run when the cells containing the information are altered.
See attached.

Included in this is a change to the procedure so the chart to be altered is passed as an argument rather than relying on the active chart object

[lmigarci]

Is there any change when having the chart in different sheet?

[Andy Pope]

Yes. The routine would need to know which sheet to check.

So I have added the min and max values to the argument list.

Please Login or Register  to view this content.

And the sheet level code can pass the relevant information

Please Login or Register  to view this content.

[lmigarci]

Hi Andy. Thx for your wise suggestions. I managed to use all the code you sent including the part for running the code when changing the cells.

I'm not able to use it when the chart is in different datasheet though. I think the problem is in defining the variables MinValue & and Maxvalue as inputs you suggested in the rutine you sent. All the cells are in sheet1 and the chart is within the same book as chart sheet.

Please Login or Register  to view this content.

How should I do that? Is there any modification in here?

Please Login or Register  to view this content.

Thx

[Andy Pope]

The AxisChange routine would be in a standard code module and is cabable of handling the first chartobject on any sheet.

But the trick to getting it to work on multiple sheets is to add the Worksheet_Change code to each sheet that has a chart and cell information.

See revised example.

[lmigarci]

Ok, it's a bit more clear!! but still i get an error about the chart objects property when trying to use the values from the datasheet to the chart. what could it be? I attached and example to clarify my question.

Thx

[lmigarci]

Hi Andy. I've tried everything which is in my hands but i'm not able to find how to find the solution when having the data and the chart in diferent data sheet.

I've got in the main module the following code:

Please Login or Register  to view this content.

Attached to the "data" datasheet this one:

Please Login or Register  to view this content.

And attached to the "chart" datasheet the same code as for the datasheet. I also tried without code in the "chart" datasheet and i made several changes concerning the name of variables (i.e. Sheets("Sheet1").Range("1")) and so on but still I get errors when running it....
Any hint.

Thank you very much.

[Andy Pope]

I can not seem to donwload your example.
Can you email it to me direct? andy AT andypoe DOT info

[lmigarci]

Sure! I sent it to andy AT andypope DOT info

[Andy Pope]

Thanks!

The revised code from your example.
Change cells A1 or A2 on Sheet1 and Chart Sheet Chart1 will be updated.

Please Login or Register  to view this content.

And for my revised workbook example this is Sheet1's Change event code

Please Login or Register  to view this content.

[lmigarci]

Ok!!! It works perfectly.

I took me several hours to try and find the solution and I couldn't....I'm getting familiar with this VBA and is getting interesting but still too much to learn about it.

THX a lot once again.

[nawzi7]

i have tried your method and its work very well.
just one question, what if i want it to autoscale again?
is there any vb or macro that i can use???

[nawzi7]

\1

As you can see in the picture above...
I've manage to make a graph of combination of scroll bar and drop down list.
But then i'm having problem with some graph which got some offset data.
So, i took your method to change the scale. Then another problem occurred as some graph didn't need to set the y-scale manually. So, i was hoping that i can do some vb @ macro that can change the Y-scale back to auto.

[DonkeyOte]

@nawzi7, welcome to the baord however please note:

Per Forum Rules do not post a question in the thread of another member -- start your own thread.
If you feel another thread is particularly relevant to your own question provide a link to the other thread within your own.