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).<pre><font color='#000000'><hr align=left width=500><font color='#000080'>Private</font><font color='#000080'>Sub</font> Worksheet_Change(ByVal Target<font color='#000080'>As</font> Range)<font color='#008000'>' Check to see if the cell(s) of the worksheet that changed</font><font color='#008000'>' include any cells that feed your chart. If they do, then</font><font color='#008000'>' we'll update your text box, but if not, don't do anything.</font><font color='#000080'>If</font><font color='#000080'>Not</font> Intersect(Target, Range("myRange")) Is Nothing<font color='#000080'>Then</font><font color='#000080'>Dim</font> idx<font color='#000080'>As</font><font color='#000080'>Integer</font><font color='#000080'>Dim</font> Total<font color='#000080'>As</font> Double<font color='#008000'>' Select your chart</font>
ActiveSheet.ChartObjects("Chart 5").Select<font color='#008000'>' Add up the values from each data label. Make sure that your</font><font color='#008000'>' data labels are set up to show VALUES (Chart Options, Data Labels</font><font color='#008000'>' tab).</font><font color='#000080'>For</font> idx = 1 To ActiveChart.SeriesCollection(1).Points.Count
Total = Total + Val(ActiveChart.SeriesCollection(1).Points(idx).DataLabel.Text)<font color='#000080'>Next</font> idx

TextBox3.Value = Total<font color='#000080'>End</font><font color='#000080'>If</font><font color='#000080'>End</font><font color='#000080'>Sub</font></font></pre>

Hope this helps,

Russell
This message was edited by Russell Hauf on 2002-04-10 16:32