Data Label Not Refreshing

Wonder if someone can help, I found the following VBA to update the color of the three charts in my report, however I have found that my data labels are not refreshing consistently.  Can the code be updated to refresh the data labels?

Sub ColorPieSlices()
' Copyright 1999 MrExcel.com
' This macro will re-color the pie slices in a chart
' So that slices for a specific category are similarly colored
' Select the chart before calling the macro
'
' Find the number of pie slices in this chart
    NumPoints = ActiveChart.SeriesCollection(1).Points.Count
    ' Loop through each pie slice
    For x = 1 To NumPoints
        ' Save the label currently attached to this slice
        If ActiveChart.SeriesCollection(1). _
            Points(x).HasDataLabel = True Then
                SavePtLabel = ActiveChart.SeriesCollection(1) _
                    .Points(x).DataLabel.Text
        Else
            SavePtLabel = ""
        End If
        ' Assign a new data label of just the point name
        ActiveChart.SeriesCollection(1).Points(x).ApplyDataLabels Type:= _
            xlDataLabelsShowLabel, AutoText:=True
        ThisPt = ActiveChart.SeriesCollection(1).Points(x).DataLabel.Text
        ' Based on the label of this slice, set the color
        Select Case ThisPt
            Case "100% Complete"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 5
             Case "Act of God"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 3
            Case "Brand- Agreement"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 7
            Case "Brand- Customs"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 22
             Case "Brand- Product"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 26
             Case "Brand- Registration"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 38
            Case "In-Budget"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 4
            Case "Landlord- Agreement"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 13
             Case "Landlord- Approvals"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 18
            Case "Landlord- Contractor"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 29
            Case "Landlord- Go"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 39
            Case "Landlord- Possession"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 21
            Case "Mast- Manufacturing Delay"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 46
            Case "Mast- Take-off Delay"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 45
            Case "Mast- Take-off Error"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 40
            Case "Mast- Transportation Delay"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 53
            Case "Partner- Approvals"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 5
            Case "Partner- Building Permits"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 8
            Case "Partner- Contractor"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 11
            Case "Partner- Customs"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 23
            Case "Partner- Operations"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 33
            Case "Partner- Parts"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 37
            Case "Partner- Registration"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 41
            Case "Partner- Site Info"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 49
            Case "Political"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 30
            Case "SD&C- Approvals"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 10
            Case "SD&C- Contractor"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 12
            Case "SD&C- Design Development"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 43
            Case "SD&C- PM"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 50
            Case "SD&C- Schematic Design"
                ActiveChart.SeriesCollection(1). _
                    Points(x).Interior.ColorIndex = 31
            Case Else
                ' Add code here to handle an unexpected label
        End Select
        ' Return the label to it's original pre-macro state
        ActiveChart.SeriesCollection(1). _
            Points(x).DataLabel.Text = SavePtLabel
    Next x
End Sub

Open in new window

jmac001Asked:
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.

andrewssd3Commented:
You say they don't update consistently - what exactly does not update?  Your code looks good for fixing what is already in the table - do you mean it does not update dynamically when values change outside the chart?

It might also help if you could post the Excel file (without sensitive data, of course)

Thanks

Stuart
0
jmac001Author Commented:
Hi the code that I am using has changed, the new code is as follows:

Sub ColorPieSlices()
Dim NumPoints As Long, x As Long
Dim SavePtLabel As String, ThisPt As String
Dim ws As Worksheet
Dim tbReasonCodes As Range
Dim Colors As Variant, Labels As Variant, v As Variant
Dim pt As Point

On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
    Set tbReasonCodes = ws.ListObjects("tbReasonCodes").DataBodyRange
    If Not tbReasonCodes Is Nothing Then Exit For
Next
On Error GoTo 0
If tbReasonCodes Is Nothing Then
    MsgBox "Couldn't find table for reason codes", vbOKOnly
    Exit Sub
End If

'Labels = tbReasonCodes.Columns(2).Value
Labels = tbReasonCodes.Columns(3).Value
Colors = tbReasonCodes.Columns(4).Value
NumPoints = ActiveChart.SeriesCollection(1).Points.Count

For x = 1 To NumPoints
    Set pt = ActiveChart.SeriesCollection(1).Points(x)
    SavePtLabel = ""
    If pt.HasDataLabel = True Then SavePtLabel = pt.DataLabel.Text
    pt.ApplyDataLabels Type:=xlDataLabelsShowLabel, AutoText:=True
    ThisPt = pt.DataLabel.Text
    Set v = Nothing
    On Error Resume Next
    v = Application.Match(ThisPt, Labels, 0)
    On Error GoTo 0
    If Not IsError(v) Then
        pt.Interior.ColorIndex = Colors(v, 1)
    End If
    pt.DataLabel.Text = SavePtLabel
Next x
End Sub

Open in new window


I would like for the pie charts to update dynamically, so now I am wondering if it makes more since to have created the pies with VBA?  Unfortunately I can not attach a sample due to sentitive info.
0
andrewssd3Commented:
I have created a test file, and it works in that it changes the colours when you change the labels or table, but you do need to run the macro.  This won't ever happen dynamically when you just change the labels or the table.  You can have the labels attached a cell value, but you'll always have to run the macro to change the colours.

You still don't say exactly what you want to happen or what actually happens. Perhaps you could do as I did and create a generic file with no sensitive data that illustrates the problem
Labels1.xlsm
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
jmac001Author Commented:
Attached s a generic pie chart of one of charts in my report.  The labeling that is not updating consistently is the data formatted label.  See pie for comments.
Sample-RC-Pie-EE-10.07.xlsm
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
Microsoft Excel

From novice to tech pro — start learning today.