Matching Primary and Secondary Axis

[Jono_NZ]

Hi,

I have a number of charts where I need to automatically ensure the secondary axis scale matches the primary axis.

I'm pretty sure I'll need to use VBA for this. Does anyone know how to help?

Thanks in advance!

 

[Damon Ostrander]

Hi Jono,

A simple macro can do this. You can either write the macro to check the selected chart, or do all the charts on a particular sheet, or process all the charts in the workbook.

You didn't mention what you definition of "matching" is. It could be axis length, min and max value, axis label text and formatting, tick placement and formatting, grid lines, number formatting, etc. Do you want all of these to match?

Damon

 

[Jono_NZ]

Hi Jono,

A simple macro can do this. You can either write the macro to check the selected chart, or do all the charts on a particular sheet, or process all the charts in the workbook.

You didn't mention what you definition of "matching" is. It could be axis length, min and max value, axis label text and formatting, tick placement and formatting, grid lines, number formatting, etc. Do you want all of these to match?

Damon

Damon,

Just the min and max values.

 

[Damon Ostrander]

Hi again Jono,

Okay, here is a macro that operates on the currently selected chart object.

Sub MakeYaxisSameAsXaxis()
   'makes the chart Value value axis (y-axis) max and min
   'scale values the same as the category (x-axis) max and min
   'values.  Operates on the currently selected chart. Note: to
   'manually select the chart, ctrl-click on the chart.
   With Selection.Chart
      .Axes(xlValue).MinimumScale = .Axes(xlCategory).MinimumScale
      .Axes(xlValue).MaximumScale = .Axes(xlCategory).MaximumScale
   End With
End Sub

Important note: to select the chart object you must Ctrl-Click on the chart.

I hope you find this helpful.

Damon