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

ID: Q231009

The information in this article applies to:

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.


CAUSE

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


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 Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/
For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp
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:


    1. Right-click the Control Toolbox and then click Additional Controls.


    2. 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:


    6. 
         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:


    9. 
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.

Additional query words: XL2000 webchart webcalc ihtmldocument2 


Keywords          : kbdta 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: August 3, 1999