Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Pie Chart with Percentage and Show it on Userform
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, -- Best Regards, Jitendra Kumar |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Pie Chart with Percentage and Show it on Userform
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Pie Chart with Percentage and Show it on Userform
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Pie Chart with Percentage and Show it on Userform
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... -- Best Regards, Jitendra Kumar "Joel" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Pie Chart with Percentage and Show it on Userform
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 Whe=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" wrote: 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... -- Best Regards, Jitendra Kumar "Joel" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Pie Chart with Percentage and Show it on Userform
Dear Joel,
I will try this code and will let you know the results.. Thanks a lot, -- Best Regards, Jitendra Kumar "Joel" wrote: 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 Whe=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" wrote: 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... -- Best Regards, Jitendra Kumar "Joel" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Pie Chart with Percentage and Show it on Userform
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, -- Best Regards, Jitendra Kumar "Jitendra Kumar" wrote: Dear Joel, I will try this code and will let you know the results.. Thanks a lot, -- Best Regards, Jitendra Kumar "Joel" wrote: 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 Whe=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" wrote: 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... -- Best Regards, Jitendra Kumar "Joel" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cylindr Chart - show percentage and value | Charts and Charting in Excel | |||
how do you show percentage labels on a bar chart? | Excel Discussion (Misc queries) | |||
Pivot Chart - show 1 row value, but also showing percentage of who | Charts and Charting in Excel | |||
Need to show percentage in a column chart | Charts and Charting in Excel | |||
Show both value and percentage on Waterfall Chart | Charts and Charting in Excel |