Create Pie Chart with Percentage and Show it on Userform

[Jitendra Kumar]

Dear Experts,

You have always helped me whenever I am in troble. so I am again here for
some help from the experts of this fantastic discussion board.

I have three columns as give below:

Category Task Task Status
Networking Conduiting - UTP Done
Networking Laying - UTP
Networking Installation - UTP Done
Networking Termination - UTP
Networking Installation - UTP Done
Networking Conduiting - UTP
Networking Laying - UTP
Networking Installation - UTP
Networking Termination - UTP
Networking Conduiting - OFC
Electrical Dedicated Earth
Electrical Ess / Non ess Power
Electrical Conduiting / Laying
Electrical Panels
Electrical Lights
Electrical Modules
Electrical UPS
Electrical Meter
Electrical Connectivity
Electrical Testing
Electrical Buffer

As you can see in the above mentioned table that my sheet contains three
columns, one is "Category", Second is "Task" and the third is "Status". What
I want is that for a given category say Networking, if there are ten tasks
defined and three of them have the status "Done" mentioned in the "Status"
column then a pie chart should be published on a userform with percentage so
that the user can see that 30% of Networking job is done.
The Category and Task can have repeted entries if the task is not completed
in a single day. so the macro should first extract the unique list of tasks
and then if some tasks have status as Done then it should publish a pie chart
report menttioning its category so that the user can figure out the status of
each of the categories.
I hope that I was able to give you all information and that you will be kind
enough to help me.

thanks a lot in advance,

 

[merjet]

This doesn't seem like a task for a pie chart (the parts comprise the
whole). A bar chart with two bars seems more apt, but you'd have to
summarize the data before creating it. You can put charts on a
UserForm using the Microsoft Office Chart control. If it's not already
in your controls Toolbox, right-click the Toolbox and then check it.

Hth,
Merjet

 

[Jitendra Kumar]

Dear Merjet,

I want to create a dynamic pie chart based on the entries done in the sheet,
place it somewhere in a sheet, export it to GIF and then import it on a
Userform as Image. I have the code for placing it on a userform but I dont
know how to dynamicaly create pie charts. please help.

 

[Joel]

I read your request earlier, but I need additional info to get it correct.

1) Do you need a seperate chart for each category? Your request asked for
"the macro should first extract the unique list of tasks " I think you meant
category.

2) You asked for a form, is it ok to move chart to a new sheet?


I would use filters and get a unique list of categories by using an advance
Filter and putting results in sheet2.

Sub MakeChart()

'clear sheet 2
Sheets("Sheet2").Cells.Clear
With Sheets("Sheet1")
'get last row on sheet 1
Sh1LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'copy uniaque cell in Sheet 1 - Col A to Sheet2 - Col A
.Range("A2:A" & Sh1LastRow).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Sheets("Sheet2").Range("A2"), _
Unique:=True
End With

With Sheets("Sheet2")
'copy header row on sheet 1 to sheet 2
Sheets("Sheet1").Rows(1).Copy Destination:=.Rows(1)
'get last row in sheet 2, unique items
Sh2LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'create a formula for cell B2 on sheet 2
PercentageFormula = _
"=sumproduct(--(sheet1!$A$2:$A$" & Sh1LastRow _
& "=A2),--(sheet1!$C$2:$C$" & Sh1LastRow & "=""Done""))/" & _
"countif(sheet1!$A:$A,A2)"
'put formula in cell B2
.Range("B2").Formula = PercentageFormula
'copy formula in B2 down column B
.Range("B2").Copy Destination:=Range("B3:B" & Sh2LastRow)
End With
End Sub

If this is what you want then I can add the chart automation or you can
Record a macro with the options you need and I can make the need
modifications to make it dynamic.

 

[Jitendra Kumar]

Dear Joel,

Thanks a lot for the help and your interest in solving my problem.

See there are various categories in the Sheet like "Networking",
"Electrical", "Infrastructure" and each category has various tasks like
"Cabling", "Conduting", etc. The user can update a task with its related
category as many times he wants untill the task is finished. if the task is
not finished then the task status field will be blank and if a task is
finished then it will be updated in the "Task Status" field as "Done". I want
a code which can extract a unique list of categories and the number of task
in each category. Now if there are 10 Tasks defined in a Category and there
are 3 Tasks with status "Done" then a pie chart should be shown on a userform
with the figures something similar as mentioned below:

Networking - 25%
Electrical - 20%
Infrastructure - 0%

The PIe Chart should be based on a dynamic range so that if the data
increases then the range of pie chart also increaes in its next execution.

Thanks a lot in advance for your kidn help...

 

[Joel]

try this code

Sub MakeChart()

'clear sheet 2
Sheets("Sheet2").Cells.Clear
With Sheets("Sheet1")
'get last row on sheet 1
Sh1LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'copy uniaque cell in Sheet 1 - Col A to Sheet2 - Col A
.Range("A2:A" & Sh1LastRow).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Sheets("Sheet2").Range("A2"), _
Unique:=True
End With

With Sheets("Sheet2")
'Create header sheet 2
.Range("A1") = "Category"
.Range("B1") = "Percentage"
'get last row in sheet 2, unique items
Sh2LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'create a formula for cell B2 on sheet 2
PercentageFormula = _
"=sumproduct(--(sheet1!$A$2:$A$" & Sh1LastRow _
& "=A2),--(sheet1!$C$2:$C$" & Sh1LastRow & "=""Done""))/" & _
"countif(sheet1!$A:$A,A2)"
'put formula in cell B2
.Range("B2").Formula = PercentageFormula
'copy formula in B2 down column B
.Range("B2").Copy Destination:=.Range("B3:B" & Sh2LastRow)
Charts.Add
ActiveChart.ChartType = xlPie
ActiveChart.SetSourceData Source:=Sheets("Sheet2").Range("A1:B" &
Sh2LastRow), _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.SeriesCollection(1).ApplyDataLabels ShowPercentage:=True
.SeriesCollection(1).DataLabels.Position = xlLabelPositionCenter
.HasTitle = True
.ChartTitle.Characters.Text = "Percentage"
End With
End With

End Sub

 

[Jitendra Kumar]

Dear Joel,

I will try this code and will let you know the results..

Thanks a lot,

 

[Jitendra Kumar]

Dear Joel..

I just tested the code and it seems to be what I needed but I have a query.
The result which is being shown by the Pie chart is not clear. I mean.. I am
not able to find if it shows the work done or the work remaining..

Can you please explain this as well

Thanks a lot in advance,