Create Pie Chart with Percentage and Show it on Userform

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jitendra Kumar - 18 May 2008 08:50 GMT

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,

Signature

Best Regards,
Jitendra Kumar

Reply to this Message

merjet - 18 May 2008 15:18 GMT

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

Reply to this Message

Jitendra Kumar - 18 May 2008 19:14 GMT

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.

Signature

Best Regards,
Jitendra Kumar

> 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
[quoted text clipped - 4 lines]
> Hth,
> Merjet

Reply to this Message

Joel - 18 May 2008 20:21 GMT

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.

> Dear Merjet,
>
[quoted text clipped - 11 lines]
> > Hth,
> > Merjet

Reply to this Message

Jitendra Kumar - 19 May 2008 08:54 GMT

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

Signature

Best Regards,
Jitendra Kumar

> I read your request earlier, but I need additional info to get it correct.
>
[quoted text clipped - 57 lines]
> > > Hth,
> > > Merjet

Reply to this Message

Joel - 19 May 2008 11:43 GMT

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

> Dear Joel,
>
[quoted text clipped - 81 lines]
> > > > Hth,
> > > > Merjet

Reply to this Message

Jitendra Kumar - 19 May 2008 14:00 GMT

Dear Joel,

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

Thanks a lot,

Signature

Best Regards,
Jitendra Kumar

> try this code
>
[quoted text clipped - 128 lines]
> > > > > Hth,
> > > > > Merjet

Reply to this Message

Jitendra Kumar - 19 May 2008 16:12 GMT

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,

Signature

Best Regards,
Jitendra Kumar

> Dear Joel,
>
[quoted text clipped - 134 lines]
> > > > > > Hth,
> > > > > > Merjet

Reply to this Message