Autosum Textbox in Chart?

[Jenawade]

I'm using a doughnut chart, with my 'data labels' containing the value of each piece of data. I currently add a text box in the center area of the doughnut and manually type in the total of the data. For example, if my doughnut has 4 values, 11, 18, 29 & 36, I manually enter their sum, 94, in a text box in the center. Every month the data changes, and I'm hoping there is a way to use 'sum of' in the center of my doughnut, or something similar.

Thanks for any & all help!
Jen

[Russell Hauf]

On 2002-04-10 12:00, Jenawade wrote:
I'm using a doughnut chart, with my 'data labels' containing the value of each piece of data. I currently add a text box in the center area of the doughnut and manually type in the total of the data. For example, if my doughnut has 4 values, 11, 18, 29 & 36, I manually enter their sum, 94, in a text box in the center. Every month the data changes, and I'm hoping there is a way to use 'sum of' in the center of my doughnut, or something similar.

Thanks for any & all help!
Jen

Here's an example. My chart is named "Chart 5" and my textbox is named "TextBox3".

Name the range where your chart gets it's data from. The range feeding my chart is named "myRange".

Now, you need to put this code in the [Worksheet that is holding the chart]'s class module (let me know if you need more clarification for this, but basically you go to the VB window, double-click on the sheet that is holding the chart, select "Worksheet" from the left drop-down at the top of the window, and "Change" from the right drop-down. When [if] you paste the code below, do not include the first line).

Private Sub Worksheet_Change(ByVal Target As Range) 

        

    ' Check to see if the cell(s) of the worksheet that changed

    ' include any cells that feed your chart.  If they do, then

    ' we'll update your text box, but if not, don't do anything.

    If Not Intersect(Target, Range("myRange")) Is Nothing Then 

        Dim idx As Integer 

        Dim Total As Double 

        

        ' Select your chart

        ActiveSheet.ChartObjects("Chart 5").Select 



        ' Add up the values from each data label.  Make sure that your

        ' data labels are set up to show VALUES (Chart Options, Data Labels

        ' tab).

        For idx = 1 To ActiveChart.SeriesCollection(1).Points.Count 

            Total = Total + Val(ActiveChart.SeriesCollection(1).Points(idx).DataLabel.Text) 

        Next idx 

        

        TextBox3.Value = Total 

        

    End If 

End Sub 

Hope this helps,

Russell

[ This Message was edited by: Russell Hauf on 2002-04-10 16:32 ]