adding background color on pie chart in vba

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!!!
eastsidemarketAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Harry LeeCommented:
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

0
eastsidemarketAuthor 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!
0
eastsidemarketAuthor Commented:
also want to add labels to the pie please.
0
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Harry LeeCommented:
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

0
eastsidemarketAuthor 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?
0
Harry LeeCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
eastsidemarketAuthor Commented:
perfect! A+ quality work right here. Pleasure and to the point. Thanks!!
0
Harry LeeCommented:
Glad I can help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.