Hiding Chart Changes

Hi
I'm not too sure what is going on here. I have a macro that produces a
couple of charts from some data in a worksheet. One of the charts needs to
be formatted as it will be copied to a Word document for presentation.
Having set the macro up so the chart format/size etc is correct I added a
line to turn off screen updating to avoid flicker and to speed up the macro.
However, as soon as I ran this revised macro the x axis labels appeared over
the x-axis labels appeared on top of the x-axis title. Thinking I had messed
up somewhere I commented out the screen updating line so I could step through
the code to see where the problem occurred - only it didn't, the chart
appeared fine. I have tried both ways a couple of times but it seems that if
screen updating is off the chart sizing does not work properly. Does anybody
have any ideas???? I am using Excel 2002 on a Windows XP Pro machine. The
code I use to size the chart is below:

With cht

'Determine which template to use
If strType = TO_DATA Then
strChartTemp = TO_TEMP
ElseIf strType = MC_DATA Then
strChartTemp = MC_TEMP
Else
MsgBox INVALID_DATA, vbExclamation, "Invalid Data"
wbkRegress.Close savechanges:=False
Exit Sub
End If

.ApplyCustomType ChartType:=xlUserDefined, TypeName:= _
strChartTemp

'Set the size of the chart object
With .Parent
.Height = 210.75
.Width = 339.75
.Border.LineStyle = 0
End With

'Set the size and position of the chart's plot area
With .PlotArea
.Top = 0
.Left = 23
.Height = 194
.Width = 325
End With

End With

Interesting behavior and I have never heard of it happening. Just
speculation, but we know that when we change one aspect of a chart (manually)
Excel will often change font sizes, etc. to try to fit everything on the
chart (sometimes this is very annoying!). It is probably part of the screen
updating that accomplishes this; if it is set off at the time you are
adjusting the chart size and plot area size perhaps you are bypassing the
processing that is fitting your axis labels and the title neatly on the chart.

In your code you are not setting any of the properties for the title or the
label; my only thought at a possible solution would be to actually get these
properties (font sizes and title location, particularly) and set them
explicitly through your code after you have set the chart size and plot area
size.
--
- K Dales


"Andy" wrote:

> Hi
> I'm not too sure what is going on here. I have a macro that produces a
> couple of charts from some data in a worksheet. One of the charts needs to
> be formatted as it will be copied to a Word document for presentation.
> Having set the macro up so the chart format/size etc is correct I added a
> line to turn off screen updating to avoid flicker and to speed up the macro.
> However, as soon as I ran this revised macro the x axis labels appeared over
> the x-axis labels appeared on top of the x-axis title. Thinking I had messed
> up somewhere I commented out the screen updating line so I could step through
> the code to see where the problem occurred - only it didn't, the chart
> appeared fine. I have tried both ways a couple of times but it seems that if
> screen updating is off the chart sizing does not work properly. Does anybody
> have any ideas???? I am using Excel 2002 on a Windows XP Pro machine. The
> code I use to size the chart is below:
>
> With cht
>
> 'Determine which template to use
> If strType = TO_DATA Then
> strChartTemp = TO_TEMP
> ElseIf strType = MC_DATA Then
> strChartTemp = MC_TEMP
> Else
> MsgBox INVALID_DATA, vbExclamation, "Invalid Data"
> wbkRegress.Close savechanges:=False
> Exit Sub
> End If
>
> .ApplyCustomType ChartType:=xlUserDefined, TypeName:= _
> strChartTemp
>
> 'Set the size of the chart object
> With .Parent
> .Height = 210.75
> .Width = 339.75
> .Border.LineStyle = 0
> End With
>
> 'Set the size and position of the chart's plot area
> With .PlotArea
> .Top = 0
> .Left = 23
> .Height = 194
> .Width = 325
> End With
>
> End With
>

Thanks for your reply,
I suspected it was something like that but wondered if anybody else had come
across it and knew of a solution.
I will have a crack at capturing the sizes and positions and see how I get
on. I guess the other alternative might be to identify the property that
causes Excel to re-position everything and then re-order my code so that the
relevant line appears as late as possible and just turn screen updating back
on a bit early...

"K Dales" wrote:

> Interesting behavior and I have never heard of it happening. Just
> speculation, but we know that when we change one aspect of a chart (manually)
> Excel will often change font sizes, etc. to try to fit everything on the
> chart (sometimes this is very annoying!). It is probably part of the screen
> updating that accomplishes this; if it is set off at the time you are
> adjusting the chart size and plot area size perhaps you are bypassing the
> processing that is fitting your axis labels and the title neatly on the chart.
>
> In your code you are not setting any of the properties for the title or the
> label; my only thought at a possible solution would be to actually get these
> properties (font sizes and title location, particularly) and set them
> explicitly through your code after you have set the chart size and plot area
> size.
> --
> - K Dales
>
>
> "Andy" wrote:
>
> > Hi
> > I'm not too sure what is going on here. I have a macro that produces a
> > couple of charts from some data in a worksheet. One of the charts needs to
> > be formatted as it will be copied to a Word document for presentation.
> > Having set the macro up so the chart format/size etc is correct I added a
> > line to turn off screen updating to avoid flicker and to speed up the macro.
> > However, as soon as I ran this revised macro the x axis labels appeared over
> > the x-axis labels appeared on top of the x-axis title. Thinking I had messed
> > up somewhere I commented out the screen updating line so I could step through
> > the code to see where the problem occurred - only it didn't, the chart
> > appeared fine. I have tried both ways a couple of times but it seems that if
> > screen updating is off the chart sizing does not work properly. Does anybody
> > have any ideas???? I am using Excel 2002 on a Windows XP Pro machine. The
> > code I use to size the chart is below:
> >
> > With cht
> >
> > 'Determine which template to use
> > If strType = TO_DATA Then
> > strChartTemp = TO_TEMP
> > ElseIf strType = MC_DATA Then
> > strChartTemp = MC_TEMP
> > Else
> > MsgBox INVALID_DATA, vbExclamation, "Invalid Data"
> > wbkRegress.Close savechanges:=False
> > Exit Sub
> > End If
> >
> > .ApplyCustomType ChartType:=xlUserDefined, TypeName:= _
> > strChartTemp
> >
> > 'Set the size of the chart object
> > With .Parent
> > .Height = 210.75
> > .Width = 339.75
> > .Border.LineStyle = 0
> > End With
> >
> > 'Set the size and position of the chart's plot area
> > With .PlotArea
> > .Top = 0
> > .Left = 23
> > .Height = 194
> > .Width = 325
> > End With
> >
> > End With
> >