Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding Chart Changes
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding Chart Changes
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filters hiding Chart | Excel Discussion (Misc queries) | |||
Hiding Zero Values on Chart | Charts and Charting in Excel | |||
Hiding text in a chart | Excel Discussion (Misc queries) | |||
Hiding a chart | Charts and Charting in Excel | |||
Hiding or Displaying Series In a Chart | Charts and Charting in Excel |