Execute macro when chart is created

  • Thread starter Thread starter Jamie Martin
  • Start date Start date
J

Jamie Martin

I want to execute a macro that will prompt me for footer information
whenever I create a chart (no more forgetting to create footers!). At the
moment I always make charts by clicking the chart button on the standard
toolbar, so I think it would be fine to tie the execution to the clicking of
that button. How do I do this? Plus, I want the macro to wait until I have
gone through the steps of the chart wizard (I guess it could come first if
it had to, but in any event the wizard should still execute).

Thanks,

Jamie
 
Jamie -

I answered this question several months ago. What I did was put a fake
chart wizard button on the toolbar which ran a macro, and at the end of
this macro, it brought up the chart wizard. Here is a partial response
from that old post, with the relevant code:

I worked out a way to "steal" the chart wizard button for your own
purposes. When opening the workbook, you should run this code to insert
a new button where the Chart Wizard button goes, give it the same
appearance as the Chart Wizard button, and then hide the real Chart
Wizard button.

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

Your new button runs the FauxChartWizard code below:

Sub FauxChartWizard()
Dim chtwiz As CommandBarControl
On Error Resume Next
Set chtwiz = Application.CommandBars.FindControl(Id:=436)
chtwiz.Execute
'' put pseudo event code below this line
End Sub

This all worked in Excel 97, so it ought to work in any version later
than that as well.

- Jon
 
Wow Jon, that is a superb hack. I tried looking on Google Groups to find an
answer to this question, but I must have used the wrong search terms.
 
I couldn't find it in google, so I may have answered it in another forum
somewhere. (It's so easy to waste time on the internet.) Fortunately I
could find the file on my hard disk.

- Jon
 
Back
Top