Microsoft provides programming examples for illustration only, without warranty either
expressed or implied, including, but not limited to, the implied warranties of
merchantability and/or fitness for a particular purpose. This article assumes
that you are familiar with the programming language being demonstrated and the
tools used to create and debug procedures. Microsoft support professionals can
help explain the functionality of a particular procedure, but they will not
modify these examples to provide added functionality or construct procedures to
meet your specific needs. If you have limited programming experience, you may
want to contact a Microsoft Certified Partner or the Microsoft fee-based
consulting line at (800) 936-5200. For more information about Microsoft Certified
Partners, please visit the following Microsoft Web site:
For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:
To work around this problem, set up the Microsoft Office Chart component programmatically. The following example illustrates how to bind the Microsoft Office Chart component to data contained in a Microsoft Office Spreadsheet component on the same user form.
| 1. | Save and close any open documents and then create a new document. |
| 2. | Start the Visual Basic Editor (press ALT+F11). |
| 3. | On the Insert menu, click UserForm. |
| 4. | Add a Microsoft Office Spreadsheet 9.0 control to the user form.
NOTE: If the Microsoft Office Spreadsheet 9.0 (or Spreadsheet) control and the Microsoft Office Chart 9.0 (or ChartSpace) controls are not visible in the Control Toolbox, follow these steps to add the controls to the Control Toolbox:| a. | Right-click the Control Toolbox and then click Additional Controls. | | b. | Click to select the Microsoft Office Chart 9.0 and Microsoft Office Spreadsheet 9.0 check boxes and then click OK. |
|
| 5. | Type the following data into the Microsoft Office Spreadsheet 9.0 control:
A1: B1: 1997 C1: 1998
A2: North B2: 12 C2: 11
A3: South B3: 14 C3: 17
A4: East B4: 16 C4: 18
A5: West B5: 14 C5: 16
|
| 6. | Add a Microsoft Office Chart 9.0 control to the user form. |
| 7. | Double-click the user form to open its Code window. |
| 8. | Type the following code into the Code window:
Private Sub UserForm_Initialize()
With ChartSpace1
' Add a chart.
.Charts.Add
' Set the data source of the chart to the Spreadsheet control.
.DataSource = Spreadsheet1
With .Charts(0)
' Create a bar chart.
.Type = chChartTypeBarClustered
' Add two data series to the chart.
.SeriesCollection.Add
.SeriesCollection.Add
' Set the properties of the first data series.
With .SeriesCollection(0)
.SetData chDimSeriesNames, 0, "B1"
.SetData chDimCategories, 0, "A2:A5"
.SetData chDimValues, 0, "B2:B5"
End With
' Set the properties of the second data series.
With .SeriesCollection(1)
.SetData chDimSeriesNames, 0, "C1"
.SetData chDimValues, 0, "C2:C5"
End With
' Display the legend.
.HasLegend = True
End With
End With
End Sub
|
| 9. | Press F5 to run the user form. |
Excel creates a bar chart using the data contained in the
Microsoft Office Spreadsheet 9.0 control. It links the chart to the
Microsoft Office Spreadsheet 9.0 control, so that changes to the spreadsheet are reflected in the chart.