ribbon customisations

User avatar
stuck
Panoramic Lounger
Posts: 8125
Joined: 25 Jan 2010, 09:09
Location: retirement

ribbon customisations

Post by stuck »

I feel way out of my depth here but you only get experience by having experience. This thread may also belong on the Excel board but I've started it here as I suspect the principles apply to the Ribbon in any Office application.

With the aid of RibbonEditor.xlam (Graphical designer for RibbonX customization. Version 1.93 Written by Andy Pope. ©Decemberl 2010) I've started building a custom group (appear at the end of the home tab). This customisation lives inside a .xlam file that holds all my macros.

I understand how to layout buttons in the group but as yet I've not been able to assign a macro to a button. I know that I need to use 'onAction=' to do this but how? Simply saying onAction="nameOfMySub", as in the code below, isn't enough as that gives an error when I click the button that the rest of the code sets up, error is 'Wrong number of arguments or invalid property assignment'.

Ken

Code: Select all

<!--RibbonX Visual Designer 1.93 for Microsoft Excel 14.0. XML Code produced on 2016/08/23-->
<customUI  xmlns="http://schemas.microsoft.com/office/2006/01/customui" >
	<ribbon >
		<tabs >
			<tab  idMso="TabHome" >
				<group 
					id="Group1"
					label="myTestGroup">
					<button 
						id="Button1"
						label="myMacro"
						onAction="nameOfSub"/>
				</group >
			</tab >
		</tabs >
	</ribbon >
</customUI >

User avatar
stuck
Panoramic Lounger
Posts: 8125
Joined: 25 Jan 2010, 09:09
Location: retirement

Re: ribbon customisations

Post by stuck »

I've worked it out :joy: ( :thankyou: example files from Ron de Bruin)

I need to update the actual Sub so that it is declared as a Ribbon thingy:

Code: Select all

Sub myMacro (control As IRibbonControl)
Now the code runs when I click the button.

Ken

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: ribbon customisations

Post by HansV »

Thanks for sharing the solution!
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: ribbon customisations

Post by Rudi »

Great advice. TX.
I wish other objects had Thingy's too. (How cool would that be!) :grin:
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
stuck
Panoramic Lounger
Posts: 8125
Joined: 25 Jan 2010, 09:09
Location: retirement

Re: ribbon customisations

Post by stuck »

Beware of what you wish for. I was doing not to bad with my customisations until about 14;30 when Something Bad happened and the Ribbon editor corrupted my file.

Oh well that's what back-ups are for. Of course the back-up was from last night so it had none of the tweaks in it but it didn't take me long to put them back.

Ken

User avatar
stuck
Panoramic Lounger
Posts: 8125
Joined: 25 Jan 2010, 09:09
Location: retirement

Re: ribbon customisations

Post by stuck »

HansV wrote:...the solution!
:hairout: unexpected side effect of the solution...

Now that I've declared:

Code: Select all

myMacro (control As IRibbonControl)
a line at the end of my macro, which only runs if the user says yes they want to do the whole thing again:

Code: Select all

Call myMacro
errors with "Compile error: Argument not optional"

As yet my Googling has not turned up any clues as to what argument is needed now that the Sub is a Ribbon thingy. Anybody here got any suggestions?

Ken

User avatar
stuck
Panoramic Lounger
Posts: 8125
Joined: 25 Jan 2010, 09:09
Location: retirement

Re: ribbon customisations

Post by stuck »

Once again The Lounge Magic (patent pending) kicks in and my Googling has found what I couldn't find before I posed the question here. The fix is to add an 'Optional ByVal' to the declaration, thus:

Code: Select all

Sub mymacro (Optional ByVal control As IRibbonControl)
Ken

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: ribbon customisations

Post by Rudi »

TX for sharing... It is much appreciated. :cheers:
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
stuck
Panoramic Lounger
Posts: 8125
Joined: 25 Jan 2010, 09:09
Location: retirement

Re: ribbon customisations

Post by stuck »

Rudi wrote:If your absence does not affect them, your presence didn't matter.
How did you know that's the real plan behind me tying up VBA magic with a Ribbon?

Ken

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: ribbon customisations

Post by Rudi »

Your strategy and foresight deserve a... ribbon! :ribbon:
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
stuck
Panoramic Lounger
Posts: 8125
Joined: 25 Jan 2010, 09:09
Location: retirement

Re: ribbon customisations

Post by stuck »

:groan:

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: ribbon customisations

Post by HansV »

Just a wild guess: is the name of the macro that doesn't work the same as the name of one of your modules?
Best wishes,
Hans

User avatar
stuck
Panoramic Lounger
Posts: 8125
Joined: 25 Jan 2010, 09:09
Location: retirement

Re: ribbon customisations

Post by stuck »

HansV wrote:..is the name of the macro that doesn't work the same as...
Good guess, I found a legacy copy of the problem macro hiding in Normal.dotm as soon as I posted, hence I pulled the plug on the post.

When I get to the end of this 'project' I'll see if I can summarise what I've learnt in a new thread. It might be useful to someone else who is starting out on this sort of thing.

Ken