adding background color on pie chart in vba

eastsidemarket
eastsidemarket used Ask the Experts™
on
hi!
how do i go about adding a light blue background color using vba on my pie chart? can't seem to get it going. also trying to figure out how to add a border on the outer box.

here is my code:

 Worksheets("test chart page").Select
    Set rng = Range("A1", Range("B" & Rows.Count).End(xlUp))
    'Range("A2:B6").Select
    rng.Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'test chart page'!$A$2:$B$6")
    ActiveChart.ChartType = xl3DPieExploded
    ActiveChart.SetElement (msoElementChartTitleCenteredOverlay)
    ActiveChart.ChartTitle.Text = "test"
    Range("D5").Select
End With

Open in new window


thanks!!!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2013

Commented:
The following code should create the 3d pie chart with 2pt border and light blue blackground.

Sub CreatePie()
Dim ShapeCnt As Integer, ChtNum As Integer
 Worksheets("test chart page").Select
    Set Rng = Range("A1:B" & Range("B" & Rows.Count).End(xlUp).Row)
    Rng.Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xl3DPieExploded
    ShapeCnt = ActiveSheet.Shapes.Count
    For ChtNum = 1 To ShapeCnt
    With ActiveSheet.Shapes(ChtNum).Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorText2
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0.6000000238
        .Transparency = 0
        .Solid
    End With
        With ActiveSheet.Shapes(ChtNum).Line
        .Visible = msoTrue
        .Weight = 2
    End With
    ActiveChart.SetElement (msoElementChartTitleCenteredOverlay)
    ActiveChart.ChartTitle.Text = "test"
    Next
End Sub

Open in new window

Author

Commented:
hi thanks HarryHYLee. getting an error when trying to run.

run time error '438' Object doesn't support this property or method.
highlighting :
.ForeColor.Brightness = 0.6000000238

Open in new window


Also where can i see a list of the themes to choose from. I want a lighter pale blue than msoThemeColorText2.

Thanks!

Author

Commented:
also want to add labels to the pie please.
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Top Expert 2013

Commented:
eastsidemarket,

which kind of data label do you want to show?

Series Name, Category Name, Value, and/or Percentage?

I have updated the code for the error on ForeColor Brightness.

Sub CreatePie()
Dim ShapeCnt As Integer, ChtNum As Integer
 Worksheets("test chart page").Select
    Set Rng = Range("A1:B" & Range("B" & Rows.Count).End(xlUp).Row)
    Rng.Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xl3DPieExploded
    ShapeCnt = ActiveSheet.Shapes.Count
    For ChtNum = 1 To ShapeCnt
    With ActiveSheet.Shapes(ChtNum).Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(85, 142, 213)
        .Solid
    End With
        With ActiveSheet.Shapes(ChtNum).Line
        .Visible = msoTrue
        .Weight = 2
    End With

'Add Data Label Series Value by default
    ActiveChart.ApplyDataLabels
'Add Percentage to Data Label
    ActiveChart.SeriesCollection(1).DataLabels.Select
    Selection.ShowPercentage = True
    
    ActiveChart.SetElement (msoElementChartTitleCenteredOverlay)
'You can either use manually entered text for the Chart Title or use object name like below
'    ActiveChart.ChartTitle.Text = "test"
    ActiveChart.ChartTitle.Text = ActiveSheet.Name
    
    Next
End Sub

Open in new window

Author

Commented:
ah looks good, thanks. I like how I can adjust the RGB now, thank you.

For data labels, just want the value , no percentages.

Can you advise?
Top Expert 2013
Commented:
If you want only the Value for Data Label,

All you need to do is delete these lines.

'Add Percentage to Data Label
    ActiveChart.SeriesCollection(1).DataLabels.Select
    Selection.ShowPercentage = True

Open in new window


The vba code should look like this.

Sub CreatePie()
Dim ShapeCnt As Integer, ChtNum As Integer
 Worksheets("test chart page").Select
    Set Rng = Range("A1:B" & Range("B" & Rows.Count).End(xlUp).Row)
    Rng.Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xl3DPieExploded
    ShapeCnt = ActiveSheet.Shapes.Count
    For ChtNum = 1 To ShapeCnt
    With ActiveSheet.Shapes(ChtNum).Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(85, 142, 213)
        .Solid
    End With
        With ActiveSheet.Shapes(ChtNum).Line
        .Visible = msoTrue
        .Weight = 2
    End With

'Add Data Label Series Value by default
    ActiveChart.ApplyDataLabels
    
    ActiveChart.SetElement (msoElementChartTitleCenteredOverlay)
'You can either use manually entered text for the Chart Title or use object name like below
'    ActiveChart.ChartTitle.Text = "test"
    ActiveChart.ChartTitle.Text = ActiveSheet.Name
    
    Next
End Sub

Open in new window

Author

Commented:
perfect! A+ quality work right here. Pleasure and to the point. Thanks!!
Top Expert 2013

Commented:
Glad I can help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial