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.

    VB:
    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 
    
    
    Formatting tags added by mark007
    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.

    VB:
    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 
    
    
    Formatting tags added by mark007
    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.

    VB:
    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 
    
    
    Formatting tags added by mark007

  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!

    VB:
    With s.Shapes.Range(shp.Name) 
        .Align msoAlignMiddles, True 
        .Align msoAlignCenters, True 
    End With 
    
    
    Formatting tags added by mark007
    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?


    VB:
    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 = [COLOR=Red]msoEmbeddedOLEObject[/COLOR] Then 
                     
                    With shp 
                        .LockAspectRatio = msoFalse 
                        [COLOR=Red].Chart.Axes(xlCategory).TickMarkSpacing = 316 
                        .Chart.Axes(xlCategory).TickLabelSpacing = 316[/COLOR] 
                    End With 
                     
                End If 
            Next shp 
        Next s 
    End Sub 
    
    
    Formatting tags added by mark007

Posting Permissions

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