Re: How do I change the X axis scale on all charts in a workbook?

J

Jon Peltier

Unless you're using Excel 2007 it's easy. Double click the X axis on one
chart, and do all your adjustments before clicking on OK. Then select the
next X axis and press the F4 function key (shortcut for Repeat Last Action).
Repeat as needed.

In Excel 2007 the F4 key no longer reliably repeats the last action.
Sometimes it does, but often I'm left repeating things the long way.

- Jon
 
J

Jon Peltier

The generic protocol for linking axis limits to cells is described here:

http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html

Instead of operating on a single chart, you would use (for embedded charts)

For Each Sht In ActiveWorkbook.Sheets
For Each ChtObj In Sht.ChartObjects
With ChtObj.Chart
' code from article above
End With
Next
Next

or (for chart sheets)

For Each Cht In ActiveWorkbook.Charts
With Cht
' code from article above
End With
Next

- Jon
 
K

KUMPFfrog

Jon,
Could you tell me how I could make something like this work for multiple
(50+) Chart Sheets with a different x-axis (min,max) and a Source Data Series
(X) Range.

I have a worksheet with 'col A' = ChartSht Name; 'col B' = X-Min; 'col C' =
Y-Max; 'col D' = m & 'col E' = n for Series ={m,n}
A B C D E
Sec1 (1) 0 1000 0 1000
Sec1 (2) 1000 2000 1000 2000
Sec1 (3) 2000 3000 2000 3000

I would like all the charts to update with this data when I run a macro
 
L

lukehalonen

This only affects the chart object within the active sheet, for me. How would I modify it to work with all charts in the workbook? I have 60+ charts I would like to update...

I am currently trying to use:

---
Sub FormatYAxis()
For Each Sht In ActiveWorkbook.Sheets
For Each ChtObj In Sht.ChartObjects
With ChtObj.Chart

' Category (Y) Axis
With .Axes(xlValue)
.MinimumScale = Range("B11").Value
.MaximumScale = Range("B12").Value
.MinorUnit = Range("B13").Value
.MajorUnit = Range("B14").Value
End With
End With
Next
Next
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top