Solved: Change Y Axis Label Position

[jmenche]

Hi,

I'm trying to loop through all of the charts in a presentation and change the Y axis label position from none to low. Can someone help? You'll also be preventing a murder because I just spent two hours trying a million different searches in Google and still cannot find it.

:beerchug:

[John Wilson]

Won't be easy but you should probably start by saying what version of PPt
Excel charts or MSGraph
Embedded or linked etc

[jmenche]

Won't be easy but you should probably start by saying what version of PPt
Excel charts or MSGraph
Embedded or linked etc

hmmm...no wonder i'm going nuts...I have 2010 and I think the charts are linked. If you double click them a spreadsheet pops up so they are either linked or embedded.

Thanks for any help. I have 299 charts :-)!

[John Wilson]

It's not really my area of expertise.

If the chart was created in PowerPoint this might work. It wont if the chart was made in Excel and pasted in. If it's a PowerPoint chart the spreadsheet that opens on double click should say "Chart in PowerPoint" Not just Excel.

Sub chex()
Dim osld As Slide
Dim oshp As Shape
For Each osld In ActivePresentation.Slides
For Each oshp In osld.Shapes
If oshp.HasChart Then
With oshp.Chart.Axes(xlValue)
.TickLabelPosition = xlLow
End With
End If
Next
Next
End Sub

[jmenche]

It turns out that my macro WAS working. I was referencing the WRONG AXIS!!! Anywho, here's the code that I was using to resize, center, and change the label position.

Sub SetLinkedChartSize()
Dim s As Slide
Dim shp As Shape
For Each s In ActivePresentation.Slides
s.Select
For Each shp In s.Shapes
If shp.Type = msoChart Then
shp.Select
With shp
.LockAspectRatio = msoFalse
.Height = 400
.Width = 500
.Chart.Axes(xlCategory).TickLabelPosition = xlLow
End With
shp.Select
Application.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
Application.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
End If
Next shp
Next s
End Sub

I should also thank Jon Peltier for something!

:beerchug:




It's not really my area of expertise.

If the chart was created in PowerPoint this might work. It wont if the chart was made in Excel and pasted in. If it's a PowerPoint chart the spreadsheet that opens on double click should say "Chart in PowerPoint" Not just Excel.

Sub chex()
Dim osld As Slide
Dim oshp As Shape
For Each osld In ActivePresentation.Slides
For Each oshp In osld.Shapes
If oshp.HasChart Then
With oshp.Chart.Axes(xlValue)
.TickLabelPosition = xlLow
End With
End If
Next
Next
End Sub

[John Wilson]

Glad you avoided murder!

Jon's the man when it comes to Excel charting!

In your code the first two selects are unecessary and slow the code. Always good to avoid selecting unless it absolutely necessary in PowerPoint!

You could if you really wanted to avoid the last selection too!

With s.Shapes.Range(shp.Name)
.Align msoAlignMiddles, True
.Align msoAlignCenters, True
End With

[mooseman]

I need this same code, but for an msoEmbeddedOLEObject, that has a chart and data sheet.
Does anyone know how to convert this?


Sub SetLinkedChartSize()
Dim s As Slide
Dim shp As Shape
For Each s In ActivePresentation.Slides
For Each shp In s.Shapes
If shp.Type = msoEmbeddedOLEObject Then

With shp
.LockAspectRatio = msoFalse
.Chart.Axes(xlCategory).TickMarkSpacing = 316
.Chart.Axes(xlCategory).TickLabelSpacing = 316
End With

End If
Next shp
Next s
End Sub