Tuesday, June 14, 2011

Custom Filled Pie Chart

I recently completed a project where the client asked me to design a pie chart which can be filled with colors according to selections made by the user. Each pie slice represents a task which the user had to complete. Once the user marks a task as "Completed" the pie slice should be filled with color, otherwise the slice should be white. Each task has a different importance and the client wanted the pie slices to be sized accordingly.

In my example there are 10 tasks. The data is arranged in columns as follows:
  • Column A - The list of tasks.
  • Column B - The status set by the user for each task.
  • Column F - Relative importance of each task (in percentage). Determines the size of the slices.
I added a pie chart with column A as the axis labels and column F as the values series. I formatted the data series in the chart to have no fill. Then I added the following code in the sheet module where the chart resides:

Private Sub Worksheet_Change(ByVal Target As Range)

PieColor = Array(10973765, 4409002, 5154185, 9394289, 11507777, 4031707, 13609363, 9606097, _
    9883065, 12426153)

If Not Intersect(Target, Range("B1:B10")) Is Nothing Then

    For i = 1 To 10

        If Cells(i, 2).Value = "Completed" Then
            ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Points(i).Interior.Color = PieColor(i - 1)
        Else
            ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Points(i).Interior.Color = -2
        End If
    
    Next i

End If

End Sub


This is how the chart looks like when tasks 1, 3, 6 and 9 are marked as completed:


(Click on the image above to view its original size)

Because the code resides in the sheet module under the Worksheet_change event, every task which the user changes to "completed" is being updated dynamically in the chart. If the user clears the "completed" status, the slice of that certain task would return to be white.

Insiders will shortly receive from Eric the file which contains this chart.