Charts - auto format the scale (x-axis) by cell ref?

[nancyo]

I tried a search, but cannot get this to work today.

Is there a way to change the format of an axis in a chart by referencing a specific cell?

By this, can the MAX or MIN on the scale be referenced to a specific cell?

 

[nancyo]

FOR THE X-AXIS:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("d39") Or Target = Range("e39") Then
ActiveSheet.ChartObjects(1).Activate
With ActiveChart.Axes(xlCategory)
If Range("d39").Value <> "" Then .MinimumScale = Range("d39").Value
If Range("e39").Value <> "" Then .MaximumScale = Range("e39").Value
End With
End If
Sheets("graph").Select
Range("C38").Select
End Sub


FOR THE Y-AXIS:

Private Sub Worksheet2_Change(ByVal Target As Range)
If Target = Range("h39") Or Target = Range("i39") Then
ActiveSheet.ChartObjects(1).Activate
With ActiveChart.Axes(xlValue)
If Range("h39").Value <> "" Then .MinimumScale = Range("h39").Value
If Range("i39").Value <> "" Then .MaximumScale = Range("i39").Value
End With
End If
Sheets("graph").Select
Range("g38").Select
End Sub

i AM SELECTING CELLS AFTER THE CODES RUN BECAUSE THE GRAPG ARE STILL 'SELECTED'.

 

[tusharm]

The reason for providing the AutoChart Manager was to protect against the kinds of problems you are facing.

You have to ensure that the worksheet / chart changes are the kind that should trigger a revision of the chart. You also need to ensure that the kinds of axes being used support min./max. values. In the non-exhaustive list of things to watch out for, you should also protect against the presence of unacceptable data in the cells linked to the min./max. values.

 

[nancyo]

I agree.

I tried to download your add-in, and it didn't work. If I open EXPLORE, I can see the file, but it is only registering as the unzip file, not the add-in. I will play around with again later...I actually tried to download it several times this am.

 

[tusharm]

Maybe, the tips on how to load the add-in will help.

http://www.tushar-mehta.com/excel/software/autochart/decompiled help/autochart0.htm

 

[nancyo]

Any suggestion on the above codes?

I was able to download the add-in and get it to work. This will be very helpful. Even tho this is available, I am still wondering why the above codes don't work...

 

[tusharm]

The test for Target being the appropriate cell is wrong. You are checking values whereas you should be checking addresses (or objects).

The worksheet_change event is an event procedure that is called by XL when it changes a worksheet cell through certain mechanisms. You cannot add worksheet2_change and expect XL to know when to call it.

In general, it is not good programming to select / activate objects. In an event procedure it is a *very, very, very* bad idea. It is also a bad idea to rely on what might or might not be the Activesheet.

Note that the code samples below compile OK but have not otherwise been tested.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ErrXIT
    Application.EnableEvents = False
    If Not (Intersect(Target, Range("d39:e39")) Is Nothing) Then
        With Target.Parent.ChartObjects(1).Axes(xlCategory)
        If Range("d39").Value <> "" Then .MinimumScale = Range("d39").Value
        If Range("e39").Value <> "" Then .MaximumScale = Range("e39").Value
            End With
    ElseIf Not (Intersect(Target, Range("h39:i39")) Is Nothing) Then
        With Target.Parent.ChartObjects(1).Axes(xlValue)
        If Range("h39").Value <> "" Then .MinimumScale = Range("h39").Value
        If Range("i39").Value <> "" Then .MaximumScale = Range("i39").Value
            End With
        End If
ErrXIT:
    Application.EnableEvents = True
    End Sub

The above code is the closest to your code that I would be willing to use. However, it carries out redundant changes to the chart scales. An alternative would be:

Option Explicit

    Sub doOneParam(whatParam, whatVal)
        If whatVal <> "" Then whatParam = whatVal
        End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ErrXIT
    Application.EnableEvents = False
    With Target.Parent.ChartObjects(1)
    Select Case Target.Address(False, False, xlA1)
    Case "d39": doOneParam .Axes(xlCategory).MinimumScale, Target.Value
    Case "e39": doOneParam .Axes(xlCategory).MaximumScale, Target.Value
    Case "h39": doOneParam .Axes(xlValue).MinimumScale, Target.Value
    Case "i39": doOneParam .Axes(xlValue).MaximumScale, Target.Value
        End Select
        End With
ErrXIT:
    Application.EnableEvents = True
    End Sub

 

[nancyo]

OK...I can follow your first code (I am not really a programmer, so I generally try to modify what suggesitons I receive or find). I will try this next.

Not really understanding some of the second code, but I will read the help and try to follow. I appreciate all your help and quick responses.

 

[btadams]

nancyo,

You were on the right track, you just needed to put all the code into the Private Sub Worksheet_Change subroutine. However Tushar's code is undoubtedly best.

 

[nancyo]

btadams - Of course, you are correct. I just merged the two and it worked.

I will also try the other code, as Tusharm is correct with his statements (especially activesheet).

I will forge ahead...what a day...

 

[agamemnon2002]

I am in similar need of this example and this code works perfectly. I would like to add and else statement regarding the min and max scale value; in the event that "H2" is blank, how would i tell the graph to return to automatic ranging for this value?

you could automate this by putting some code like this in the Worksheet_Change event. In this example the x-axis of a chart named "Chart 1" will be scaled to whatever the user enters into cells G2 and H2

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("G2") Or Target = Range("H2") Then
ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveChart.Axes(xlCategory)
If Range("G2").Value <> "" Then .MinimumScale = Range("G2").Value
If Range("H2").Value <> "" Then .MaximumScale = Range("H2").Value
End With
End If
End Sub

if you don't know what your chart name is, try replacing the chart name with an index number, ActiveSheet.ChartObjects(1).Activate