ExcelBanter - Setting up default chart properties

Craig -

You can modify charts in VBA easily enough. Turn on the macro recorder,
then modify one, to see what kind of code you'd use.

Detecting a chart creation event is problematic. I've never figured out
what event is fired when a chart sheet is created or a chart is embedded
in a sheet.

But you can intercept the chart wizard. These two macros do the trick.
The first one substitutes a lookalike button for the chart wizard
button, which runs the second macro. The second macro actually runs the
chart wizard, but when the wiz is done, you still have control.

Sub ReplaceChartWizardButton()
Dim MyButton As CommandBarButton
Set MyButton = CommandBars("Standard").Controls.Add _
(Type:=msoControlButton, _
befo=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

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
ActiveChart.Parent.Left = 0
End Sub

Apply whatever formatting you need where I inserted the line
ActiveChart.Parent.Left = 0

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

CraigB wrote:
Is it possible to modify some of the default chart properties through
VBA somehow? - I'm thinking particlarly about the border property of
the plot area and legend. These are common to all types of chart so I
can't use the "Default Chart" because that's too specific - I need to
modify the base set of initialisation properties that Excel uses when
inserting a new chart, so that no matter what type of chart the user
wants to create, it doesn't have a border.

Alternatively, is there an event I can listen on for when a new chart
is inserted so that I could run some code to fix these properties each
time.

Thanks in advance

Craig