ACC2002: How to Programmatically Rotate a Chart in PivotChart View (304171)

MORE INFORMATION

1. Start Microsoft Access, and then open the sample database Northwind.mdb.
2. Open a new, blank form in Design view.
3. In the toolbox, make sure that the Control Wizards button is enabled, and then add a subform/subreport control to the form. The Subform Wizard will start.
4. On the first page of the Subform Wizard, click Use existing Tables and Queries, and then click Next.
5. On the second page of the wizard, click the arrow in the Tables/Queries box, and then click the Category Sales for 1997 query.
6. Add the CategorySales and CategoryName fields to the Selected Fields box, and then click Next.
7. On the last page of the wizard, name the subform MyChart, and then click Finish.
8. On the View menu, click Properties.
9. Click the arrow in the box at the top of the property sheet, and then click Form.
10. Click the Format tab, and then set the DefaultView property to Single Form.
11. Close the property sheet.
12. Make the subform a little bigger so that you can work with it better.
13. Double-click the form footer section of the subform to open the property sheet for the form footer section. For the box at the top of the property sheet, make sure that Form is selected.
14. On the Format tab, set the DefaultView property to Pivot Chart, and then close the property sheet.
15. Save the form, and then switch the form to Form View.
16. If the field list is not visible, right-click the chart and then click Field List.
17. Drag the CategoryName field from the field list to the box on the chart that reads Drop Category Fields Here.
18. Drag the CategorySales field from the field list to the box on the chart that reads Drop Data Fields Here.
19. Save the form, and then switch the form back to Design view.
20. Add a command button to the form with the following properties:

    Caption: Rotate Chart
    OnClick: =RotateChart()

21. Click the form, and then, on the View menu, click Code to open the module of the form.
22. On the Tools menu, click References, and then add a reference to the Microsoft Office XP Web Components. If this reference is not listed, click the Browse button to browse to the following file, and then click OK:

    C:\Program Files\Common Files\Microsoft Shared\WebComponents\10\OWC10.dll.

23. Close the References dialog box.
24. Add the following code to the module of the form:

    Function RotateChart()
    
      Dim objPivotChart As OWC10.ChChart
      Dim objChartSpace As OWC10.ChartSpace
      Dim frmChart As Access.Form
      Dim OldRotation As Integer
      Dim NewRotation As Integer
      Dim TopDegree As Integer
      
      Set frmChart = Me.Controls("MyChart").Form
      Set objChartSpace = frmChart.ChartSpace
      Set objPivotChart = objChartSpace.Charts.Item(0)
      
      'Set type of chart to a 3D Column.
      objPivotChart.Type = chChartTypeColumn3D
      
      ' Determine the initial rotation setting.
       OldRotation = objPivotChart.Rotation
       
      ' Determine the number of degrees you have to count up to.
       TopDegree = OldRotation + 360
    
      'The following code will rotate the chart 360 degrees.
      For NewRotation = OldRotation To TopDegree Step 12
          'Determine if the Rotation must be adjusted to fit under 360
          'degrees.
          If NewRotation > 360 Then
            objPivotChart.Rotation = NewRotation - 360
          Else
            objPivotChart.Rotation = NewRotation
          End If
          DoEvents
          DoCmd.RepaintObject
      Next
    End Function
    					

25. Compile and save the code. Then, close the Visual Basic Editor.
26. Switch the form to Form view, and then click the command button. Note that the chart rotates 360 degrees.