Problem setting Height property in Powerpoint

[vbMonk]

I'm working with Powerpoint 2003, running on WinXP. I have Presentations that contain slides with multiple embedded Charts on the slides. I'm trying to automate formatting certain aspects of the charts and the shapes that contain them.

I've written the following procedure to make the changes I want:

Private Sub Format_4_Charts()
'set the slide object to the current slide
Set oSlide = ActiveWindow.View.Slide

'loop through the shapes on the current slide
For Each oShape In oSlide.Shapes
If oShape.Type = msoEmbeddedOLEObject Then
If oShape.OLEFormat.ProgID = "Excel.Chart.8" Then
Set oChart = oShape.OLEFormat.Object.charts(1)
oChart.legend.Font.Size = 10
Set oAxis = oChart.Axes(xlCategory)
With oAxis
.TickLabels.Font.Size = 12
End With
With oShape
'align the charts
If .Top < 100 Then
'top two charts
.Top = 25
Else
'bottom two charts
.Top = 265
End If
'align the left sides of the charts
If .Left < 100 Then
'left charts
.Left = 0
Else
'right charts
.Left = 350
End If
'set the size of the chart
.Height = 250.5
.Width = 374.25
End With
End If
End If
Next
Debug.Print ""
'clear the objects
Set oSlide = Nothing
Set oShape = Nothing
Set oChart = Nothing
Set oAxis = Nothing
End Sub

The first changes I make are to the chart itself, changing the font of the legend and the x axis labels.

Next, I resize and align the shapes containing the charts.

If I run this as is, I have to run it three times before the change to the .Height property takes.

If I remove the code to modify the chart fonts, changing the .Height property takes the first time.

If I run the two changes in separate procedures, but during the same run, it takes three runs to get the .Height property to change.

If I run the two changes in two separate runs, the change to the .Height property takes on the first try.

Finally, it doesn't seem to matter what order I make any of the changes; if I do them all in a single run, it takes 3 times for the change to the .Height property to take.

Can somebody explain to me what it is about the changes to the chart fonts that prevents the change to the .Height property from taking effect until the 3rd run? What am I missing?

 

[SkipVought]

Hi,

Excel charts has AutoScale as a default in the ChartArea.

Set AutoScale to xlFalse ASAP in the process, and it will probably solve several of your problems.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[vbMonk]

Mmm, seems easier said than done. If I add this line,...

oChart.AutoScaling = False

...I get a run time error, "Method of 'AutoScaling' of object '_Chart' failed". Am I not trying to set this property correctly?

 

[SkipVought]

    With oChart
        .ChartArea.AutoScaleFont = False

    End With

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[vbMonk]

Thanks. Unfortunately, it didn't resolve the original problem. Bummer.

 

[SkipVought]

I'd think that you'd have to loop thru the shapes to get/resize the rectangles within which your charts "reside", top, left, width, height, stored in an array.

then loop thru Shapes collection again to get the charts and, based on the location of each, reposition/resize the charts, using the data in the array.

2 loops in one procedure.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[vbMonk]

I've tried that and it still doesn't work. I've even tried separate procedures. The only thing that really works is to run the two things in completely separate runs, i.e. as two separate scripts.

I've done some more noodling and determined that the mere act of setting the oChart object is enough to cause this this to require two runs to complete (that's a correction of what I stated above BTW; it's two runs to make it work; the third was just to see the results...sorry for any confusion). Anyway, this line, by itself is enough to cause the problem:
Set oChart = oShape.OLEFormat.Object.Charts(1)
It doesn't even require that I do anything with oChart. It's weird.