Excel Hints

Results 1 to 7 of 7

Thread: Solved: Change Y Axis Label Position

  1. #1

    Solved: Change Y Axis Label Position

    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.


  2. #2
    Won't be easy but you should probably start by saying what version of PPt
    Excel charts or MSGraph
    Embedded or linked etc
    John Wilson
    Microsoft PowerPoint MVP
    Amazing Free PowerPoint Tutorials
    http://www.pptalchemy.co.uk/powerpoi...tutorials.html

  3. #3
    Quote Originally Posted by 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
    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 :-)!

  4. #4
    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.

    [VBA]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[/VBA]
    John Wilson
    Microsoft PowerPoint MVP
    Amazing Free PowerPoint Tutorials
    http://www.pptalchemy.co.uk/powerpoi...tutorials.html

  5. #5

    Thanks John

    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.

    [vba]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[/vba]

    I should also thank Jon Peltier for something!





    Quote Originally Posted by 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.

    [vba]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[/vba]

  6. #6
    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!

    [vba] With s.Shapes.Range(shp.Name)
    .Align msoAlignMiddles, True
    .Align msoAlignCenters, True
    End With[/vba]
    Last edited by John Wilson; 03-29-2012 at 07:07 AM.
    John Wilson
    Microsoft PowerPoint MVP
    Amazing Free PowerPoint Tutorials
    http://www.pptalchemy.co.uk/powerpoi...tutorials.html

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


    [vba]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[/vba]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •