Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Execute macro when chart is created
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, _ 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 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 ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Jamie Martin wrote: 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Execute macro when chart is created
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. "Jon Peltier" wrote in message ... 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, _ 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 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 ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Jamie Martin wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Execute macro when chart is created
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 ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Jamie Martin wrote: 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. "Jon Peltier" wrote in message ... 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, _ 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 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 ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Jamie Martin wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Execute Macro | Excel Discussion (Misc queries) | |||
Auto Execute Macro | Excel Worksheet Functions | |||
Auto-execute macro.... | Excel Discussion (Misc queries) | |||
Execute Macro | Excel Discussion (Misc queries) | |||
button to execute macro | Excel Programming |