View Full Version : Newbie problem: button calling macro which applies custom chart

2016-06-02, 01:00 PM
Hi All

I'm a newbie with VBA so probably I am missing something completely obvious! I would really appreciate any help - I've previously created simple custom ribbons with button calling macros or applying styles, but I don't know how to handle this issue.

Excel 2010 on Windows 7 Enterprise. Editing the CustomUI part using XML Notepad.

I have created several custom chart templates and saved them in the default C:\Users/[username]/AppData/Roaming/Microsoft/Templates/Charts/ directory.

I want a button on my custom ribbon for each of these chart templates which will apply the template to the selected chart.

The XML looks like this:

<group id="ChartFormats" label="Custom chart formats">
<button id="DoughnutChart" label="Doughnut Chart" size="large" imageMso="ChartTypeOtherInsertGallery" onAction="ApplyDoughnut"/>

and the VBA looks like this:

Public MyRibbon As IRibbonUI

Sub MyRibbonInit(ribbon As IRibbonUI)
Set MyRibbon = ribbon
End Sub

Sub ApplyDoughnut(ribbon As IRibbonUI)
ActiveChart.ApplyChartTemplate ( _
End Sub

The macro works fine when I run it as a macro (without the ribbon As IRibbonUI bit). Other macros being called via custom buttons work (e.g. applying fills to shapes).

But when I click on the button to run ApplyDoughnut, I get a Type Mismatch error. Including the .full filepath to the crtx file doesn't work.

Any help would be really, really appreciated.

Andy Pope
2016-06-04, 10:40 AM
Try modifying the argument signature of your callback for the button.

'Callback for Button1 onAction
Sub ApplyDoughnut(control As IRibbonControl)

ActiveChart.ApplyChartTemplate ( _ "Doughnut.crtx")
End Sub