Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Autosum Textbox in Chart?

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Michigan USA
    Posts
    231
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •