| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Formatting pie charts via VBA
Hi,
I'm currently struggling to change the Title and the Legend of pie charts that are generated by passing data from Word to Excel, then copying the chart over to Word. (I'm using Word + Excel 2000). The VBA is use works OK, and generates a reasonable looking chart. However, no matter what I try, I cannot alter the chart title, or the legend. Let me expand on this a little. 1. The Chart Title is always centered. However, the legend (if there a lot of entries in the chart) spills over the title and obscures it. Therefore, I need to position the title on the far left of the chart. 2. If I have more than, say, eight elements in the chart, the legend spills beyond the bottom of the chart, but there seems to be huge spacing gaps between each entry on the legend. However, if I create a chart using an Excel Range and the Chart Wizard, it seems to easily accomodate ten or more entries, and space them nice and evenly. It does not seem to matter whether I set "Legend.AutoScaleFont" to true or false, or whether I try and position the title. It always shows up the exactly the same. I thought I had cracked this, but I obviously need to do some fine tweaking. Any suggestions appreciated. Code below: //// cNumRows and cNumCols are variables used to populate an Excel Range from a Word VBA array of data Set oChart = oSheet.ChartObjects.Add.Chart oChart.SetSourceData Source:=oSheet.Range("A1").Resize(cNumRows, cNumCols), PlotBy:= _ xlColumns oChart.ChartType = xl3DPieExploded oChart.RightAngleAxes = True oChart.PlotArea.Height = 215 oChart.PlotArea.Width = 215 oChart.PlotArea.left = 5 oChart.PlotArea.Fill.Visible = False oChart.PlotArea.Fill.Visible = False oChart.PlotArea.Border.LineStyle = -4142 oChart.Elevation = 30 oChart.Rotation = 80 oChart.Pie3DGroup.VaryByCategories = True oChart.HasTitle = True oChart.ChartTitle.Top = 0 oChart.ChartTitle.left = 0 oChart.ChartTitle.Characters.Text = "Current Asset Allocation" oChart.ApplyDataLabels Type:=xlDataLabelsShowPercent, LegendKey:=False _ , HasLeaderLines:=True oChart.SeriesCollection(1).DataLabels.Font.Size = 8 oChart.HasLegend = True With oChart.Legend .Legend.Shadow = True '.Legend.Position = xlLegendPositionRight .Legend.AutoScaleFont = False .Legend.Font.Size = 3 End With Thanks Neil. |
|
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Formatting pie charts via VBA
Does your code raise an error (assuming no error suppression is on) or does it just not work? It simply does not work. No error messages are displayed. Again, when I tried it with your tweaked code, it made no difference. In fact if I set the font size to 30 instead of 3, it makes no difference. The legend remains exactly the same. Having looked through the documentation, most of the code seems correct, but Excel is not co-operating. The version of Word/Excel I am using is 9.02720 if this makes any difference. Thanks Neil. Andy Pope wrote: Hi, Your chart title positioning code works for me in a test environment. The legend part I had to tweak slightly. oChart.HasLegend = True With oChart.Legend .Shadow = True .Position = xlLegendPositionRight .AutoScaleFont = False .Font.Size = 3 End With Does your code raise an error (assuming no error suppression is on) or does it just not work? Cheers Andy wrote: Hi, I'm currently struggling to change the Title and the Legend of pie charts that are generated by passing data from Word to Excel, then copying the chart over to Word. (I'm using Word + Excel 2000). The VBA is use works OK, and generates a reasonable looking chart. However, no matter what I try, I cannot alter the chart title, or the legend. Let me expand on this a little. 1. The Chart Title is always centered. However, the legend (if there a lot of entries in the chart) spills over the title and obscures it. Therefore, I need to position the title on the far left of the chart. 2. If I have more than, say, eight elements in the chart, the legend spills beyond the bottom of the chart, but there seems to be huge spacing gaps between each entry on the legend. However, if I create a chart using an Excel Range and the Chart Wizard, it seems to easily accomodate ten or more entries, and space them nice and evenly. It does not seem to matter whether I set "Legend.AutoScaleFont" to true or false, or whether I try and position the title. It always shows up the exactly the same. I thought I had cracked this, but I obviously need to do some fine tweaking. Any suggestions appreciated. Code below: //// cNumRows and cNumCols are variables used to populate an Excel Range from a Word VBA array of data Set oChart = oSheet.ChartObjects.Add.Chart oChart.SetSourceData Source:=oSheet.Range("A1").Resize(cNumRows, cNumCols), PlotBy:= _ xlColumns oChart.ChartType = xl3DPieExploded oChart.RightAngleAxes = True oChart.PlotArea.Height = 215 oChart.PlotArea.Width = 215 oChart.PlotArea.left = 5 oChart.PlotArea.Fill.Visible = False oChart.PlotArea.Fill.Visible = False oChart.PlotArea.Border.LineStyle = -4142 oChart.Elevation = 30 oChart.Rotation = 80 oChart.Pie3DGroup.VaryByCategories = True oChart.HasTitle = True oChart.ChartTitle.Top = 0 oChart.ChartTitle.left = 0 oChart.ChartTitle.Characters.Text = "Current Asset Allocation" oChart.ApplyDataLabels Type:=xlDataLabelsShowPercent, LegendKey:=False _ , HasLeaderLines:=True oChart.SeriesCollection(1).DataLabels.Font.Size = 8 oChart.HasLegend = True With oChart.Legend .Legend.Shadow = True '.Legend.Position = xlLegendPositionRight .Legend.AutoScaleFont = False .Legend.Font.Size = 3 End With Thanks Neil. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
|
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Formatting pie charts via VBA
Hi,
If you want you can send me, offline, your file and I will take a look. Cheers Andy wrote: Does your code raise an error (assuming no error suppression is on) or does it just not work? It simply does not work. No error messages are displayed. Again, when I tried it with your tweaked code, it made no difference. In fact if I set the font size to 30 instead of 3, it makes no difference. The legend remains exactly the same. Having looked through the documentation, most of the code seems correct, but Excel is not co-operating. The version of Word/Excel I am using is 9.02720 if this makes any difference. Thanks Neil. Andy Pope wrote: Hi, Your chart title positioning code works for me in a test environment. The legend part I had to tweak slightly. oChart.HasLegend = True With oChart.Legend .Shadow = True .Position = xlLegendPositionRight .AutoScaleFont = False .Font.Size = 3 End With Does your code raise an error (assuming no error suppression is on) or does it just not work? Cheers Andy wrote: Hi, I'm currently struggling to change the Title and the Legend of pie charts that are generated by passing data from Word to Excel, then copying the chart over to Word. (I'm using Word + Excel 2000). The VBA is use works OK, and generates a reasonable looking chart. However, no matter what I try, I cannot alter the chart title, or the legend. Let me expand on this a little. 1. The Chart Title is always centered. However, the legend (if there a lot of entries in the chart) spills over the title and obscures it. Therefore, I need to position the title on the far left of the chart. 2. If I have more than, say, eight elements in the chart, the legend spills beyond the bottom of the chart, but there seems to be huge spacing gaps between each entry on the legend. However, if I create a chart using an Excel Range and the Chart Wizard, it seems to easily accomodate ten or more entries, and space them nice and evenly. It does not seem to matter whether I set "Legend.AutoScaleFont" to true or false, or whether I try and position the title. It always shows up the exactly the same. I thought I had cracked this, but I obviously need to do some fine tweaking. Any suggestions appreciated. Code below: //// cNumRows and cNumCols are variables used to populate an Excel Range from a Word VBA array of data Set oChart = oSheet.ChartObjects.Add.Chart oChart.SetSourceData Source:=oSheet.Range("A1").Resize(cNumRows, cNumCols), PlotBy:= _ xlColumns oChart.ChartType = xl3DPieExploded oChart.RightAngleAxes = True oChart.PlotArea.Height = 215 oChart.PlotArea.Width = 215 oChart.PlotArea.left = 5 oChart.PlotArea.Fill.Visible = False oChart.PlotArea.Fill.Visible = False oChart.PlotArea.Border.LineStyle = -4142 oChart.Elevation = 30 oChart.Rotation = 80 oChart.Pie3DGroup.VaryByCategories = True oChart.HasTitle = True oChart.ChartTitle.Top = 0 oChart.ChartTitle.left = 0 oChart.ChartTitle.Characters.Text = "Current Asset Allocation" oChart.ApplyDataLabels Type:=xlDataLabelsShowPercent, LegendKey:=False _ , HasLeaderLines:=True oChart.SeriesCollection(1).DataLabels.Font.Size = 8 oChart.HasLegend = True With oChart.Legend .Legend.Shadow = True '.Legend.Position = xlLegendPositionRight .Legend.AutoScaleFont = False .Legend.Font.Size = 3 End With Thanks Neil. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Date formatting on a Pivot Chart's Data Table | Charts and Charting in Excel | |||
| Conditional Formatting in Bar Charts with Labeling | Charts and Charting in Excel | |||
| Formatting Charts from Pivots | Excel Worksheet Functions | |||
| Pivot charts - Losing formatting | Charts and Charting in Excel | |||
| Custom charts - default formatting | Charts and Charting in Excel | |||
Linear Mode
