VBA Chart

millhouse123

Active Member
Joined
Aug 22, 2006
Messages
334
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

Code:
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
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Your problem is caused because the width and height of the PlotArea are greater than those of the ChartObject.
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
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

Active Member
Joined
Aug 22, 2006
Messages
334
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,057
Messages
5,599,545
Members
414,316
Latest member
ExcelLee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top