VBA and graphs

[Geordie]

Hi All,

Does anyone know what VBA code to use so that I can use cell refs to set the scale on the axis of a graph?

e.g. In cell A1 I set the max and in Cell A2 I set the min value of my x axis?

Many thanks
M

 

[Richard Schollar]

Assuming this is on a separate chart sheet then you can use something like:

ActiveChart.Axes(xlCategory,xlPrimary).MaximumScale = Worksheets("MyWorksheetName").Range("A1").Value

likewise for the minimum scale

 

[Geordie]

Mmm I have about 20 graphs on 1 excel sheet. Is there a way to name the graphs and reference them by name in the code?

Thanks so much for any help!
M

 

[Geordie]

Can anyone let me know if this is possible?
Cheers
M

 

[Richard Schollar]

Hi

Do all the charts require the same scale, or are they all different?

 

[SydneyGeek]

I'm not sure you can name charts. You generally refer to charts by their index number. For embedded charts in a worksheet you need to loop through the ChartObjects collection.
Here's an example with the following assumptions:
1. You don't have any gaps in the number series (ie, you have not deleted any charts)
2. All data sets are evenly spaced

Sub AxisScales()
    Dim iMax As Integer
    Dim iMin As Integer
    Dim iTick As Integer
    Dim iJump As Integer
    Dim i As Integer
    
    iJump = 7 'rows between data sets
    
    For i = 1 To 4
        'Values for axes are in Column F.
        'First set is in rows 2,3,4; subsequent sets
        'are offset by iJump
        iMax = Cells(2 + iJump * (i - 1), 6).Value
        iMin = Cells(3 + iJump * (i - 1), 6).Value
        iTick = Cells(4 + iJump * (i - 1), 6).Value
        ActiveSheet.ChartObjects(i).Select
        With ActiveChart.Axes(xlValue, xlPrimary)
            .MaximumScale = iMax
            .MinimumScale = iMin
            .MajorUnit = iTick
        End With
    Next i
End Sub

This works for 4 charts. If you have 20, or you don't start with Chart1, change the loop to suit. You will need to adjust the cell positions for your layout.

Denis