Microsoft Office Forums - View Single Post - Type Mismatch when RibbonX applying crtx to Chart?

Elinor · 06-03-2016, 06:45 AM

Apologies, I just realised what's wrong! Should have talked to my duck first...

Sub ApplyDoughnut(control As IRibbonControl)
ActiveChart.ApplyChartTemplate "Doughnut.crtx"
End Sub

The red bit is what it shoul be, I had somehow copied "ribbon As IRibbonUI" instead.

Please feel free to delete or leave up as a lesson to others.

ORIGINAL POST BELOW:

Hi All

I'm a newbie with VBA and RibbonX 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:

Code:

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

and the VBA looks like this:

Code:

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

    Sub ApplyDoughnut(ribbon As IRibbonUI)
        ActiveChart.ApplyChartTemplate "Doughnut.crtx"
    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 help.

Is there some sort of interface or class or something (you can see how much of a newbie I am, please be gentle!) that I need to declare somewhere?

Any help would be really, really appreciated.