VBA Chart

[millhouse123]

I am trying to automate 2 pie charts on the same Worksheet but I cannot get them to be the same size even though all of the size properties are the same. Any idea why the second pie chart would be much smaller than the first. I have never used VBA with charts so any advice on cleaning up the code would be helpful too.

Thanks

Charts.Add
ActiveChart.ChartType = xlPie
ActiveChart.SetSourceData Source:=WS1.Range("B11").Resize(Rowsize, 2), PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Current VS. Target"

ActiveChart.HasLegend = False
      
    With Selection.Border
        .LineStyle = xlNone
    End With
    
    Selection.Interior.ColorIndex = xlNone
    
    ActiveChart.ChartArea.Select
    With Selection.Border
        .LineStyle = xlNone
    End With
    
    ActiveChart.PlotArea.Select
    With Selection.Border
        .Weight = xlThin
        .LineStyle = xlNone
    End With
    
    Selection.Interior.ColorIndex = xlNone
     
   With ActiveSheet.ChartObjects(1)
        .Top = 322.5
        .Left = 70
        .Width = 337.5
        .Height = 264
   End With
   
   With ActiveChart.PlotArea
        .Top = 231.75
        .Left = 175
        .Width = 400
        .Height = 326.5
    End With
    
         
   
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.ChartArea.Select
    ActiveChart.ApplyDataLabels AutoText:=True, LegendKey:=False, _
        HasLeaderLines:=True, ShowSeriesName:=False, ShowCategoryName:=True, _
        ShowValue:=True, ShowPercentage:=True, ShowBubbleSize:=False
    With Selection.Font
        .Size = 12
    End With
   
   Range("I25").Select

'Workbooks("Consolidator.xls").Activate
'Worksheets("Summary_Report").Activate

'WS2.Activate 'must be active so chart is put in right spot.
Charts.Add
ActiveChart.ChartType = xlPie
ActiveChart.SetSourceData Source:=WS1.Range("B11").Resize(Rowsize, 5), PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Current VS. Target"
ActiveChart.HasLegend = False
      
    With Selection.Border
        .LineStyle = xlNone
    End With
    
    Selection.Interior.ColorIndex = xlNone
    
    ActiveChart.ChartArea.Select
    With Selection.Border
        .LineStyle = xlNone
    End With
    
    ActiveChart.PlotArea.Select
    With Selection.Border
        .Weight = xlThin
        .LineStyle = xlNone
    End With
    
    Selection.Interior.ColorIndex = xlNone

    With ActiveChart
        Do Until .SeriesCollection.Count = 0
            .SeriesCollection(1).Delete
        Loop
    End With
    
     With ActiveChart.SeriesCollection.NewSeries
        .Values = WS1.Range("G11").Resize(Rowsize, 1)
        .XValues = WS1.Range("B11").Resize(Rowsize, 1)
    End With
    
        
   With ActiveSheet.ChartObjects(2)
        .Top = 322.5
        .Left = 450
        .Width = 337.5
        .Height = 264
   End With
   
   With ActiveChart.PlotArea
        .Top = 231.75
        .Left = 175
        .Width = 400
        .Height = 326.5
    End With
   
       ActiveChart.SeriesCollection(1).Select
    ActiveChart.ChartArea.Select
    ActiveChart.ApplyDataLabels AutoText:=True, LegendKey:=False, _
        HasLeaderLines:=True, ShowSeriesName:=False, ShowCategoryName:=True, _
        ShowValue:=False, ShowPercentage:=True, ShowBubbleSize:=False
            With Selection.Font
                .Size = 12
           End With

 

[Andrew Poulsom]

Your problem is caused because the width and height of the PlotArea are greater than those of the ChartObject.

 

[millhouse123]

Thanks - I can't believe I missed that obviious answer.

 

[tusharm]

Andrew may be right but (1) Excel is generally good about working around bad user specifications, and (2) you have the same code for both charts. So, any problem with one should affect the other. Or maybe not?

Be that as it may, I cannot replicate the problem. With XL2003 I get very similar looking charts. Maybe, the problem has something to do with the contents of B, C, and/or G?

What is strange about the code is that in the first instance you are plotting B:C as the y-values for 2 separate series in the pie chart. Of course, all you will see is B.

In the 2nd chart, you are plotting B as the x values (and G as the y-values).

I am not sure if that has anything to do with the discrepancies.

What do B, C, and G contain?

 

[millhouse123]

Tusharm,

Thank you for your input. I have changed plot area so it is smaller than the Chart area and it seems to work fine now.

Column B contains a descritpion, Column C contains % of Total and Column G Contains the Target % of total.

In reguards to your comment if I understand you right - you don't feel that the first chart should work - the reason this works is because the columns are adjacent. where in the second chart they are seperated.

Now I am trying to change the Data Labels to size 12 font and I can't get that to work. Below is the snipit of code I recorded but cannot get to work when I put it in each chart. (it actaully works if I step through the code rather than just run it straight through.

ActiveChart.SeriesCollection(1).DataLabels.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 12
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With