Modify chart size in slide using VBA

[Rudi]

Hi all,

I need help to design a macro that will modify the size of a chart in a slide. I do not know the objects in PPT and have tried and failed to get a reference to the activechart on a slide and then size its height and width. Help please!! Any sample code will be grateful.

TX

PS: Ideally it would be great to have the code loop through all the slides, and if there is a chart, to size it and center it on the slide. But even sample code just to size (and center) the chart on each slide individually will also be appreciated.

[steveh]

Hi all,

I need help to design a macro that will modify the size of a chart in a slide. I do not know the objects in PPT and have tried and failed to get a reference to the activechart on a slide and then size its height and width. Help please!! Any sample code will be grateful.

TX

PS: Ideally it would be great to have the code loop through all the slides, and if there is a chart, to size it and center it on the slide. But even sample code just to size (and center) the chart on each slide individually will also be appreciated.

Morning Rudi

I presume (very dangerous) that resizing a chart would be the same as resizing a picture, if that is the case there is an example here

Resize Pictures

Not being very good with VBA this might be a red herring but I just placed a picture on a slide and activated the Macro Recorder, showed the guide grid using view, resized a picture, manually moved the picture to the centre, closed the grid guides. I then manually made the picture the same size as the slide and ran the macro and it did as asked. I do not know though how to stipulate all slides.

Sub ResizeNCentre()
'
'

Code: Select all

 Macro recorded 17/06/2011 by steve
'

    ActiveWindow.Selection.SlideRange.Shapes("Picture 4").Select
    With ActiveWindow.Selection.ShapeRange
        .Fill.Transparency = 0#
        .Height = 212.5
        .Width = 272.62
    End With
    With ActiveWindow.Selection.ShapeRange
        .IncrementLeft 90.75
        .IncrementTop 86.5
    End With
    ActiveWindow.Selection.ShapeRange.IncrementLeft 5.12
    ActiveWindow.Selection.ShapeRange.IncrementLeft 5.75
    ActiveWindow.Selection.ShapeRange.IncrementLeft 5.62
    ActiveWindow.Selection.ShapeRange.IncrementLeft 5.75
    ActiveWindow.Selection.ShapeRange.IncrementLeft 5.62
    ActiveWindow.Selection.ShapeRange.IncrementLeft 5.62
    ActiveWindow.Selection.ShapeRange.IncrementLeft 5.75
    ActiveWindow.Selection.ShapeRange.IncrementTop 2.88
    ActiveWindow.Selection.ShapeRange.IncrementTop 5.75
    ActiveWindow.Selection.ShapeRange.IncrementTop 5.62
    ActiveWindow.Selection.ShapeRange.IncrementTop 5.62
    ActiveWindow.Selection.ShapeRange.IncrementTop 5.75
    ActiveWindow.Selection.ShapeRange.IncrementTop -0.62
    ActiveWindow.Selection.ShapeRange.IncrementTop -0.62
    ActiveWindow.Selection.ShapeRange.IncrementTop -0.62
    ActiveWindow.Selection.ShapeRange.IncrementTop -0.62
    ActiveWindow.Selection.Unselect
End Sub

HTH in some way

Editted to add: Reading through the recorded macro above maybe activepresentation instead of activewindow, I will go and have a try

[Rudi]

How dod you record a macro in powerpoint, unless this is unavailable in 2010. I have PPT 2010 and there is no record option??

Thanks steve...this is a good starting point for me. Appreciate the code!!

[steveh]

Hi Rudi

In Office 2007 and 2010 I think you have to go to options and activate the developer tab to be able to use the macro recorder.

I am working away on a very slow dongle at the moment but one quick Google turned up this MSKB Article which might help with the basics of macros in PowerPoint

Editted: 06:40 18.06.11 - More information:

Having just read the article it seems MS have a new feature in Office 2010, no macro recorder you can however record a macro in the proper 2003 version of PowerPoint if you still have it somewhere and then copy and paste it into the downgraded version of PowerPoint

The article does show how to loop through a presentation and delete shapes so maybe you could use the looping examples and my resize and centre example to make one Macro.

If you do not have 2003 anymore tell me the dimensions that you want your charts to be and I will record a macro for that size and centre

[Rudi]

Thanks for your willingness to help Steve...it is commendable!

I have managed to get a working macro with your code acting as a good starting point. I ended up with:

Code: Select all

Sub ChartMoveAndSize()
    Dim s
    For Each s In ActiveWindow.Selection.SlideRange.Shapes
    Select Case s.Type

'================================================
'Comment out whichever "TYPE" is not applicable
'================================================

    Case msoPicture
            s.LockAspectRatio = msoFalse
            s.Height = 300  'Modify height ratio
            s.Width = 600   'Modify width ratio
            s.Top = 120       'Modify top position ratio
            s.Left = 60      'Modify left position ratio
        
    Case msoLinkedPicture
            s.LockAspectRatio = msoFalse
            s.Height = 300  'Modify height ratio
            s.Width = 600   'Modify width ratio
            s.Top = 0       'Modify top position ratio
            s.Left = 0      'Modify left position ratio
        
    Case msoEmbeddedOLEObject
            s.LockAspectRatio = msoFalse
            s.Height = 300  'Modify height ratio
            s.Width = 600   'Modify width ratio
            s.Top = 0       'Modify top position ratio
            s.Left = 0      'Modify left position ratio
        
    Case msoLinkedOLEObject
            s.LockAspectRatio = msoFalse
            s.Height = 300  'Modify height ratio
            s.Width = 600   'Modify width ratio
            s.Top = 0       'Modify top position ratio
            s.Left = 0      'Modify left position ratio
    End Select
    Next
End Sub

Now the next project is to get it into an add-in format since PPT does not have a "Personal Macro SlideFile" so that it can run for any presentation.