Question re: Programming the Excel Chart Wizard

[Dave G]

I have written a Macro to replace the Chart Wizard that will format my
Charts the way my company wants them (VBA sample below). I have a
separate function named "On_Graph" that detects whether you have a
chart selected or not and will format the chart rather than run the
wizard. When the wizard is called from the macro it will run fine if
you have selected your data range. However, if you try and change the
data range or series in step 2 of the wizard the macro crashes. I
guess the wizard must stop and start again when you select a new data
range, I am not too sure, and my macro tries to continue. If anyone
has an idea about a better way to run the chart wizard and capture the
output using VBA or a way to pause the macro to wait for the output,
please let me know. Thanks. DG

If On_Graph = False Then
Application.CommandBars("Chart").Controls("&Chart Wizard").Execute
End If
If On_Graph = True Then
If ActiveChart.ChartType = xlLine _
Or ActiveChart.ChartType = xlLineMarkers _
Or ActiveChart.ChartType = xlLineStacked _
Or ActiveChart.ChartType = xlLineMarkersStacked _
Or ActiveChart.ChartType = xlLineStacked100 _
Or ActiveChart.ChartType = xlLineMarkersStacked100 _
Or ActiveChart.ChartType = xl3DLine Then......

[Jon Peltier]

Dave -

I developed a technique a while back that hijacked the chart wizard.
First you write a macro that runs the chart wizard, then after the
wizard is done, it performs other actions on the chart. Then you remove
the chart wizard button from the command bar, replace it with a button
with the same button face, which calls the new macro.

This procedure replaces the regular chart wizard with the fake one. You
could call it from the Workbook_Open event.

Sub ReplaceChartWizardButton()
Dim MyButton As CommandBarButton
Set MyButton = CommandBars("Standard").Controls.Add _
(Type:=msoControlButton, _
before:=CommandBars("Standard").Controls("&Chart Wizard") _
.Index + 1)
With MyButton
.Caption = "Fake Chart Wizard"
.Style = msoButtonIcon
.OnAction = "FauxChartWizard"
.FaceId = 1957
End With
CommandBars("Standard").Controls("&Chart Wizard").Visible = False
End Sub

This is the program called by the fake chart wizard button.

Sub FauxChartWizard()
Dim chtwiz As CommandBarControl
On Error Resume Next
Set chtwiz = Application.CommandBars.FindControl(Id:=436)
chtwiz.Execute
'' dummy command to see if it works
'' put your own code here instead
ActiveChart.Parent.Left = 0
End Sub

I just tried this code, both to create a new chart (with & without the
range selected prior to running it), and to adjust an existing chart
(including changing of the data source range), and I didn't have the
problem you described.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

[Dave G]

Thanks Jon. I figured out that my macro runs fine. The problem was
that I had turned application.screenupdating to false and the Chart
Wizard crashed when it needed to go back to the sheet to get a data
range. I did use your suggestion to call the Chart Wizard using its
Control ID (Application.CommandBars.FindControl(Id:=436).Execute),
which saves me from needing to know which toolbar the wizard button is
currently residing on. Thanks for the help.

Dave

Jon Peltier <jonpe...@yahoo.com> wrote in message news:<3ED18CCD...@yahoo.com>...