Insert pie chart in Excel macro function

Question

I am new to macro function. I have been trying to insert a pie chart on the Subject_name fields (X,Y,Z) in Excel via macro function. The user should be able to plot this graph when clicking a macro command button. Basically, it should count and then use the number to plot the chart.

Here is the example of current data:

Channel_Type | Channel_Category | Category | **Subject_Name**
  MICROBLOG          General         A           X
   Forum             General         A           Z
   BLOG              General         A           Y
   FORUM             General         A           X
  MICROBLOG          General         A           Z
   BLOG              General         A           Z

Here is the desired outcome:

X : 2 | Y : 1 | Z : 3

This is what i tried before to plot the graph for the whole sheet but i am unsure of how to just select one column:

Option Explicit

Sub Example()
Dim cht As ChartObjects
For Each cht In Worksheets(1).ChartObjects

Next cht
cht.Chart.ChartType = xlPie


End Sub

I have been stuck for awhile. Appreciate any help.

(1) First of all this is not a free coding service, so you will need to show us your code and tell where you got stuck (read How to Ask) (2) Did you try to use the macro recorder while creating this chart, so you get an idea of how to start coding a macro. (3) I guess you will need to calculate the percentages first, so this would be your first task (I would do that with formulas instead of VBA) Also I see no need for VBA here I think everything can be done with formulas. — Pᴇʜ
– Pᴇʜ, Commented Sep 19, 2017 at 6:22
Thank you for the comment. My apologies for not being clear enough. I have edited the question. I m trying to use VBA as it should be automatically plotting the chart after clicking the command button. — Faheera
– Faheera, Commented Sep 19, 2017 at 6:36
Your question is still unclear. Posting code without explaining anything won't work. Your code is doing nothing I guess. I suggested some things but you didn't even try one of them (we will help you but we don't do the work for you). So again, (1) calculate (with formulas in the sheet) the values you want to show in the chart. This is the first step needed, without these values no chart! (2) Generate the chart manually. (3) Then use the macro recorder as I suggested (while generating the chart) to get an idea how to start a code. — Pᴇʜ
– Pᴇʜ, Commented Sep 19, 2017 at 6:45
Hi. i figure out how to code using the macro reader. Thank you very much — Faheera
– Faheera, Commented Sep 19, 2017 at 8:01

teylyn · Accepted Answer · 2017-09-19 06:53:08Z

2

Before you can plot a chart, you need to aggregate the data and provide the numbers that you want to plot in a chart. A chart does not do that automatically and no chart can plot text values.

There are many ways to aggregate the data into the three number values you need for the pie chart, for example a pivot table or formulas using Countif. Or you could write VBA that loops over the text values and aggregates the count into three variables.

Once the numbers have been generated, you can use them to create a chart.

Any of the above can be done manually or with VBA. If you use the Pivot approach, VBA may not even be required. It depends on your circumstances.

answered Sep 19, 2017 at 6:53

teylyn

36.2k6 gold badges58 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

QHarr · Accepted Answer · 2017-09-19 07:01:34Z

1

Consider setting your data up in a table which includes a count column at the end. You can enter =1 into this (assuming each row is a unique record) and it will auto-fill down for each row. Create a pivot chart (pie chart) off the 'Subject name' and 'count' fields. Then you can tie the refresh of the pivottable to a command button (if you must use VBA) and the chart will update at a button push. The data being in an Excel table means it will also pick up new rows as added.

answered Sep 19, 2017 at 7:01

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Comments

Pᴇʜ · Accepted Answer · 2017-09-19 07:02:24Z

0

You can do this completely without VBA and even have it updated automatically.

The formula in H2 is =COUNTIF(D:D,G1) and copied down up to H4. The chart was just inserted manually through menu:

select G1:H4
menu => insert => chart (select 2D circle)
select the template you like

This chart will update automatically if you add entries in column A-D.

No VBA!

answered Sep 19, 2017 at 7:02

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

Collectives™ on Stack Overflow

Insert pie chart in Excel macro function

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related