Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

OFF2000: Inserting a Chart Control on a UserForm Does Not Start the Chart Wizard


View products that this article applies to.

This article was previously published under Q231009

↑ Back to the top


Symptoms

When you insert a Microsoft Office Chart component on a UserForm object, the Chart Wizard does not appear even though the user form contains a valid data source for the Microsoft Office Chart 9.0 control.

↑ Back to the top


Cause

This problem occurs because user forms do not support the Document Object Model (DOM).

↑ Back to the top


Workaround

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.

↑ Back to the top


Keywords: KB231009, kbprb, kbpending

↑ Back to the top

Article Info
Article ID : 231009
Revision : 5
Created on : 11/23/2006
Published on : 11/23/2006
Exists online : False
Views : 294