RibbonX and a "macro may not be available" head-scratcher

[mdupris]

So I have a simple .xlsm file with a couple very simple macros in it.
I've edited the customUI with the following code:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/
customui">
<ribbon startFromScratch="true">
<tabs>
<tab id="CustomTab" label="Trial Tab">
<group id="SimpleControls" label="Trial Group">
<button id="Button1" imageMso="HappyFace" size="large"
label="Say Hi"
onAction="sayHi3" />
<button id="Button2" imageMso="HappyFace" size="large"
label="ThisSayHi3"
onAction="Thisworkbook.sayHi3" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>

When I open it, sure enough the tab is there with two buttons. When I
hit the "Say hi" one I get the message: "Wrong number of arguments or
invalid property assignment" which is reasonable I guess since I
haven't qualified the macro name. Hitting the "ThisSayHi3" button
gives the message "Cannot run the macro "Thisworkbook.sayHi3". The
macro may not be avialable in this workbook or all macros may be
disabled." This is more mysterious. The macro is there; macros are
enabled; and it runs fine using F5 within the VB Editor. It's a just
one-liner at this point invoking MsgBox().
I haven't found anything useful relating to this message on the
Microsoft site, or MSDN, or (sigh) here. (The conditions mentiond in
support.microsoft.com/kb/930076 article dosn't apply here.) So, while
I suspect it's something blatantly obvious to others who know this
RibbonX, it's baffling to me. Any hints out there about what's going
on here?

= Marchand =

 

[Tom Ogilvy]

I don't have xl2007 installed, but Ron de Bruin has put up a page on working
with the ribbon. Perhaps you will see something in his working code:

http://www.rondebruin.nl/ribbon.htm

 

[mdupris]

Tom,

I don't have xl2007 installed, but Ron de Bruin has put up a page on working
with the ribbon. Perhaps you will see something in his working code:

http://www.rondebruin.nl/ribbon.htm

 

[mdupris]

Tom,

Thanks for the reference. Hasn't helped, but there's definitely a
lot of useful stuff there.

= M =

 

[Ron de Bruin]

Hi M

When you click on the "generate callbacks button it create the macros for you
Copy/paste them in a module in your workbook and add the code

There is a note about this in the xml examples

 

[mdupris]

Thanks for your response, Ron. I'm now cycling back to this project
and worked out an answer to the problem. It's rather much of a kludge,
but apparently a sanctioned one (http://msdn.microsoft.com/msdnmag/
issues/07/02/ribbonx/). The trick seems to be NOT to use the all-too-
tempting "onAction" parameter in the XML file, but rather use the
(poorly documented) "Tag" parameter. Thus, if I re-write my XML to
look like this:

<button id="customButton1" label="sayHi Label" size="large"
onAction="RibbonXOnAction" imageMso="Bold" tag="sayHi" />

and include this macro in the VBA:

Sub RibbonXOnAction(button As IRibbonControl)
Application.Run button.Tag
End Sub

then the macro "sayHi" will run without complaint.
I certainly hope others can find easier, more direct ways of doing
something as simple as connecting a macro to a Ribbon button -- and if
so, that they share them -- but this is getting me started.

= Marchand =