Create Pie Chart with Percentage and Show it on Userform
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" wrote:
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.
--
Best Regards,
Jitendra Kumar
"merjet" wrote:
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
|