# VBA and graphs

#### Geordie

##### Board Regular
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
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
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

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

#### Richard Schollar

##### MrExcel MVP
Hi

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

#### SydneyGeek

##### MrExcel MVP
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

Replies
1
Views
187
Replies
4
Views
118
Replies
1
Views
119
Replies
7
Views
293
Replies
1
Views
172

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.

### Which adblocker are you using?

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

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