Creating charts in Excel with VBA, this code *should* work...

I'm hoping someone has an answer for this:

VB Code:

Private Sub CmdChart_Click()
 
'Create the new chart
Dim Newchart As Chart
 
Set Newchart = Charts.Add '(After:=Charts(Charts.Count))
 
'Change the Name
Newchart.Name = "Pipeline Summary"
 
'Create a series for the chart
 
Dim TheSeries As Series
Newchart.SeriesCollection.Add _
Source:=Worksheets("Pipeline").Range("K$12:N$50") 'chart data range
 
Set TheSeries = Newchart.SeriesCollection(1)
 
'Change the chart type
TheSeries.ChartType = xl3DPie
'Change the series title
TheSeries.Name = "Pipeline Info"
 
'Data formatting
With TheSeries
.XValues = _
   Worksheets("Pipeline").Range("K$11:N$11") 'chart labels
   .HasDataLabels = True
    .DataLabels.ShowValue = True
    .DataLabels.Font.Italic = True
    .DataLabels.Font.Size = 14
End With
 
'Modify the legend
With Newchart
.HasLegend = True
.Legend.Font.Size = 14
End With
 
'modify the 3D view
With Newchart
.HasLegend = True
.Elevation = 45
End With
 
'format the chart title
With Newchart.ChartTitle
.Font.Bold = True
.Font.Size = 18
.Border.LineStyle = XlLineStyle.xlContinuous
.Border.Weight = XlBorderWeight.xlMedium
End With
 
'format the plot area
With Newchart.PlotArea
.Interior.Color = RGB(255, 255, 255)
.Border.LineStyle = XlLineStyle.xlLineStyleNone
.Height = 450
.Width = 450
.Top = 75
.Left = 25
End With
End Sub

The chart itself displays, but only contains the values of K12:K50, instead of everything from K12 - N50. I need it to display the SUM of the values in K12-K50, L12-L50, etc until N12-N50, then display those totals in the chart. Can someone help?

If the data is not sensitive, or if you have test values, can you post the xls zipped?

sure, here ya go. It uses a database to retrieve the information from. Also, I've noticed null values seem to confuse it a bit :S

steve

I have had a look. I do not think that the pie chart is the one you want, and the series seem to be completely uh useless on a piechart.

I think that for the piechart view to work you need to add all the peoples fees together by section. Then you can make a pie chart on the resulting lines.

Eg>

Code:

---

Name Commission        Mortgage Fee        Life Fee        Home Insurance Fee
Bob      100.00                       
Jane      300.00                        599.22       
Tim      200.00                              200.00

---

would become

Code:

---

Total Commission        Mortgage Fee        Life Fee        Home Insurance Fee
Total 600                                      599.22        200

---

Which the pie chart actually means something.

Perhaps I am missing what you are looking for though :) sorry!
Excel intellisense is crap for charting isn't it!

Ok, if not a pie chart to show the individual values then, what sort of chart to use? I've tried using a bar chart, but the values still dont display properly. If I used a bar chart (or similar) to display all the information, what would the code be? I'd figured out I *don't* need to display the totals of the columns, just the values!

Perhaps a chart like this? (sheet type 1)

As I said, I do not know what you are trying to show to managers, but perhaps this is something more like what you want.
Also - your ranges should be only for those with data, and any nulls should be completed with 0's (find replace?)

We're along the right lines, but it has to be dyanmic (like when another advisor is searched for and their information displayed from the search function). Leave it with me...

Well once you've decided how to display it, you can do the dynamic search and show bit :)


Good luck with it, post up if you finish it :)