XL2000: Problems Using Code to Display ChartWizard on PivotTables

If you attempt to use Visual Basic for Applications code to display the Chart Wizard dialog box for a chart, you may receive the following error message:

This problem occurs when the following conditions are true:

• You select a PivotChart (a chart that is made from a PivotTable report) rather than a standard chart.
  
  -and-
  
  
• You use the following Visual Basic for Applications code:

  Application.Dialogs(xlDialogChartWizard).Show
  						

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements. To work around this problem, use the XL4 type macro function
which you can call from Visual Basic for Applications code.

To create a Visual Basic for Applications macro that calls a Microsoft Excel version 4.0 macro to show the Chart Wizard dialog box, follow these steps:

1. In a Microsoft Excel workbook, on the Tools menu, point to Macro, and then click Visual Basic Editor.
2. On the Insert menu, click Module. Type the following code in the module:

   Sub RunMe()
       Run ("ShowWizard")
   End Sub
   					

3. On the File menu, click Close and Return to Microsoft Excel.
4. Insert a 4.0 Macro sheet in the workbook by pressing CTRL+F11.
5. On the 4.0 Macro sheet, type the following code:

   A1: ShowWizard
   A2: =CHART.WIZARD?(TRUE)
   A3: =RETURN()
   					

6. On the 4.0 Macro sheet, place your cursor in cell A1, and follow these steps:
   1. On the Insert menu, point to Name, and then click Define.
   2. In the Define Name dialog box, click ShowWizard in the Names in workbook list.
   3. Under Macro, click Command.
      
      The Category is User Defined by default.
   4. Click OK.
7. Save the workbook.
8. Select a chart sheet with a PivotChart.
9. On the Tools menu, point to Macro, and then click Macros.
10. In the Macro dialog box, click RunMe, and then click Run.
    
    You should receive a Chart Wizard dialog box for the PivotChart.

NOTE: You may encounter two problems using CHART.WIZARD?(TRUE) to display the Chart Wizard dialog box on PivotTables:

• In the Chart Wizard dialog box, Step 5 of 5 may display a blank sample.
  
  Although the sample is blank, all other functionality of step 5 is available and Excel correctly makes the chart based on your choices in steps 2 and 3.
  
  -and-
  
  
• In Step 5 of 5 you cannot use the Back button to return to earlier steps.
  
  To work around this problem, click Cancel and restart the macro.

Using TRUE as the first argument in the CHART.WIZARD? macro function creates the five step Chart Wizard dialog box. If you omit TRUE or use FALSE instead, the macro will halt with a macro error on this command when you run it on a PivotChart. When you run the macro on a chart which is not a PivotChart, and you omit TRUE or use FALSE as the first argument, the function will create a two step Chart Wizard dialog box.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

The Visual Basic for Applications code in the "Cause" section works correctly on a chart that is not created from a PivotTable report.