XL2000: Problems Using Code to Display ChartWizard on PivotTables

ID: Q218986

The information in this article applies to:

Microsoft Excel 2000

SYMPTOMS

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:

Run-time error '1004':
Show method of Dialog class failed

CAUSE

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 
```

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, use the XL4 type macro function


=CHART.WIZARD?(TRUE)

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:

In a Microsoft Excel workbook, on the Tools menu, point to Macro, and then click Visual Basic Editor.

On the Insert menu, click Module. Type the following code in the module:


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

On the File menu, click Close and Return to Microsoft Excel.

Insert a 4.0 Macro sheet in the workbook by pressing CTRL+F11.

On the 4.0 Macro sheet, type the following code:


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

On the 4.0 Macro sheet, place your cursor in cell A1, and follow these steps:

On the Insert menu, point to Name, and then click Define.

In the Define Name dialog box, click ShowWizard in the Names in workbook list.

Under Macro, click Command.

The Category is User Defined by default.

Click OK.

Save the workbook.

Select a chart sheet with a PivotChart.

On the Tools menu, point to Macro, and then click Macros.

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.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

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

Additional query words: XL2000

Keywords : kbdta kbdtacode kbchart OffVBA xlpivot KbVBA
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbbug