Changing Chart Colours In Forms

[boblovesyousomuch]

Hello,

I have a pie chart that generates on the fly from information from a table in my database. I would like to be able to put another coloumn of colours in so when the pie chart generates each segment is a colour I have defined. However I don't have a clue how to do it?

Any help greatly appreciated.

Carl

 

[spongie1]

Could I see a snippet of your code for getting the data from the table and generating the pie chart? I did something similar but not the same and I might be able to help if I see what you are doing.

 

[boblovesyousomuch]

Hi,

I have not really wrote it yet so I am open to suggestion and change.

Carl

 

[spongie1]

Ok, I have not seen much information on this forum about pivotcharts and I have only been programming in vba since a the end of November, so what I ask of you is feedback as you learn more vba. I want to know how to improve my code.

Here is what you can do, and it will work because I have written and tested it. However, I have only tested it using Access 2002.

First, create your table.
Next, use the form wizard to create a form... make the selections necessary to select view as pivot chart and also select the table.

Now, create a second form in the design view. Add a button and set the on click event to procedure. Then add code similar to this to the vba procedure linked to the button.

Private Sub Command0_Click()

  Dim db As Database
  Dim rs As Recordset
  Dim objPivotChart As OWC10.ChChart
  Dim objChartSpace As OWC10.ChartSpace
  Dim frm As Access.Form
  Dim strExpression As String
  Dim objSeries As OWC10.ChSeries
  Dim frmName As String
  Dim values
  
  Set db = CurrentDb()
  
  frmName = "frmpSalary"
  'Open the form in PivotChart view.
  DoCmd.OpenForm frmName, acFormPivotChart
  Set frm = Forms(frmName)
  
  'use this if you want it to format your chart for landscape printing (not necessary for viewing)
  frm.Printer.Orientation = acPRORLandscape
  
  Set rs = frm.Recordset
  
  'Loop through Recordset to obtain data for the chart and put in strings.
  rs.MoveFirst
    'rs.Fields start at 0 and continue to the number of fields you have in your table -1
    'Just make the values field the number corresponding to the column in the table you want to use as your data.
    Do While Not rs.EOF
        strExpression = strExpression & rs.Fields(1).Value & Chr(9)
        values = values & rs.Fields(2).Value & Chr(9)
        rs.MoveNext
    Loop
  rs.Close
  Set rs = Nothing
  
  'Trim any extra tabs from string
  strExpression = Left(strExpression, Len(strExpression) - 1)
  values = Left(values, Len(values) - 1)
  
  
  'Clear existing Charts on Form if present and add a new chart to the form.
  'Set object variable equal to the new chart.
  Set objChartSpace = frm.ChartSpace
  objChartSpace.Clear
  objChartSpace.Charts.Add
  
  'Set Titles
  Set objPivotChart = objChartSpace.Charts.Item(0)
  objPivotChart.HasTitle = True
  objPivotChart.Title.Caption = "Wages"
  
  'Add Series to Chart and set the caption.
  objPivotChart.SeriesCollection.Add
  
  With objPivotChart.SeriesCollection(0)
    .Caption = "Wages"
    .Type = chChartTypePie
  'Add Data to the Series
    .SetData chDimCategories, chDataLiteral, strExpression
    .SetData chDimValues, chDataLiteral, values
 
    'I don't know anything about setting colors
    'Once you get this working tell me where you found the color information
    .Points.Item(0).Interior.Color = 1
    .Points.Item(1).Interior.Color = 111112
    .Points.Item(2).Interior.Color = 55555533
  End With
  
  frm.SetFocus
  Set frm = Nothing

Finally, add some references. In the vba editor... Tools... References...

I added Micorsoft Graph 10.0 Object Library and
Microsoft DAO 3.6 Object Library. Also, you should probably have stuff like Microsoft ActiveX Data Objects 2.5 Library.

Like I said I have only been at this for a couple of weeks so every day I am learning a lot more than I knew the day before. I would sincerely appreciate constructive feed back on code structure and references and colors.

I am not sure what the other references are that you might need as I emailed myself code I developed on another computer but forgot to include a list of reference.

Hope this helps. Like I said. I know the code works.

The next time I read this forum, I will probably know if it is possible to do the same thing without imbedding it in a form first.

 

[boblovesyousomuch]

Thank you very much, there is a lot there and I appreciate it.Will look at it soon and let you know. Currently working on something else so haven't got time at the moment.

Cheers

Carl