Changing scale axis

**lmigarci** · 05-27-2008, 03:38 AM

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** · 05-27-2008, 04:43 AM

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

Code:

    With ActiveChart.Axes(xlValue)
        .MinimumScale = Range("A1")
        .MaximumScale = Range("A2")
    End With

**lmigarci** · 05-27-2008, 06:08 AM

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** · 05-27-2008, 06:50 AM

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

Code:

Sub ChangeAxis()

    With ActiveChart.Axes(xlValue)
        .MinimumScale = Range("A1")
        .MaximumScale = Range("A2")
    End With

End sub

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** · 05-27-2008, 08:54 AM

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

**Andy Pope** · 05-27-2008, 09:58 AM

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

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

Code:

Sub AxisChange(Cht As Chart, MinValue, MaxValue)
    With Cht.Axes(xlValue)
        .MinimumScale = MinValue
        .MaximumScale = MaxValue
    End With
End Sub

And the sheet level code can pass the relevant information

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Range("A1:A2"), Target) Is Nothing Then
        AxisChange ActiveSheet.ChartObjects(1).Chart, Range("A1").Value, Range("A2").Value
    End If
    
End Sub

**lmigarci** · 05-28-2008, 03:32 AM

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.

Code:

Sub AxisChange(Cht As Chart, MinValue, MaxValue)
    With Cht.Axes(xlValue)
        .MinimumScale = MinValue
        .MaximumScale = MaxValue
    End With
End Sub

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

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Range("A1:A2"), Target) Is Nothing Then
        AxisChange ActiveSheet.ChartObjects(1).Chart, Range("A1").Value, Range("A2").Value
    End If
    
End Sub

Thx

**Andy Pope** · 05-28-2008, 05:03 AM

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** · 05-28-2008, 05:34 AM

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** · 05-29-2008, 09:59 AM

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:

Code:

Sub AxisChange(Cht As Chart, MinValue, MaxValue)
    With Cht.Axes(xlValue)
        .MinimumScale = MinValue
        .MaximumScale = MaxValue
    End With
End Sub

Attached to the "data" datasheet this one:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Range("A1:A2"), Target) Is Nothing Then
        AxisChange ActiveSheet.ChartObjects(1).Chart, Range("A1").Value, Range("A2").Value
    End If
    
End Sub

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** · 05-29-2008, 10:07 AM

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

**lmigarci** · 05-29-2008, 10:21 AM

Sure! I sent it to andy AT andypope DOT info

**Andy Pope** · 05-29-2008, 10:31 AM

Thanks!

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

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Range("A1:A2"), Target) Is Nothing Then
        AxisChange Chart1, Range("A1").Value, Range("A2").Value
    End If
    
End Sub

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

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Range("A1:A2"), Target) Is Nothing Then
        AxisChange Worksheets("Sheet3").ChartObjects(1).Chart, Range("A1").Value, Range("A2").Value
    End If
    
End Sub

**lmigarci** · 05-29-2008, 10:58 AM

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.