VBA and graphs

Geordie

Board Regular
Joined
Oct 16, 2006
Messages
143
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Assuming this is on a separate chart sheet then you can use something like:

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

likewise for the minimum scale
 

Geordie

Board Regular
Joined
Oct 16, 2006
Messages
143
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
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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
Code:
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
 

Forum statistics

Threads
1,181,102
Messages
5,928,073
Members
436,587
Latest member
Slicesofquince

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top