How to create a custom ribbon Addin for Excel 2010
30. May 2012 20 Comments
Creating a custom ribbon addin for Excel can be done in a few steps
- Develop your VBA code in a new Excel sheet, save it as Excel addin .xlam
- Open the addin with Custom UI Editor to create Buttons, Groups, etc. and link it with your VBA code
- Add the addin directory to the secure folders in excel
- Configure Excel to load your addin at startup
Here are the required steps in detail
Start Excel and develop your VBA code as you did before. If you do not see a development tab in excel, activate it via the options menu. File > Options > Ribbon > On the right side check “Development Tools”
- You need to create an event handler to make your functions callable from the UI. Here is an example for a function and an event handler. The event handler foo_eventhandler calls the function foo which displays a message box with “Hello World from Excel”.
Sub foo_eventhandler(control As IRibbonControl)
foo
End SubSub foo()
MsgBox ("Hello World from Excel")
End Sub
Save your Excel as Excel Addin (.xlam) to your C:\Users\YOURNAME\AppData\Roaming\Microsoft\Addins directory. You can save it wherever you want, but the predefined addins directory might be good idea.
Yet the addin does not have any UI elements. You have to define these elements by yourself. Download and install the Custom UI Editor from http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2009/08/07/7293.aspx . Start the editor and open your previous saved .xlam file. The editor shows a blank text field. From the Menu > Insert > Sample XML choose Excel Custom Tab. Use the onAction property at the button to call an eventhandler. Here is an example code to call the foo_eventhandler
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon>
<tabs>
<tab id="customTab" label="MyTab" insertAfterMso="TabHome">
<group id="customGroup" label="MyGroup">
<button id="customButton1" label="Say Hello" size="large"
onAction="foo_eventhandler" imageMso="HappyFace" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>
Start Excel from the program menu. Go to File > Options > Security Center > Settings > Secure Locations > Add Location, and add your addins directory e.g. C:\Users\YOURNAME\AppData\Roaming\Microsoft\Addins as secure directory to load stuff from. Close Excel.
When you open the .xlam file using Excel, you can already use your addin. However, it is not available when you start Excel from the programs menu. To load the addin when excel starts, go to the development tab > Add-Ins and check your addin. (The form displays addins from the Appdata\Roaming\Microsoft\Addins directory)
Now the Addin is loaded when Excel starts. Whenever you change something and the Addin does not appear anymore, go and check your xml UI code. Make sure that all IDs are unique and do not have blanks
(e.g. id=”My Button2” is a bad idea)
More Icons
There are many icons available you can use for your addins. For example download the icon gallery addin from http://www.accessribbon.de/index.php?Downloads:24 . To change the icon on button modify the imageMso property in your xml file.
<button id="customButton1" label="Say Hello" size="large"
onAction="foo_eventhandler" imageMso="HappyFace" />
Have fun!
About erpcoder
ErpCoder 
Hi,
I’ve tried tp create the add-in following your detailed instructions and I can make it but the issue blow ups when I try to use the button I’ve just created: it happens to appear the below message:
“Cannot run the macro ‘foo_eventhandler’. The macro may not be available in this workbook or all macros may be disabled.”.
I’ve obviously enabled all the macros.
Can you help me??
Thank you so much.
Giulio
did you check if the macro code is realy part of your workbook. I made some strage experience with Excel, that saving the vb code does not always work correctly and the code was not saved
Thank you so much. I did it.
This post was really helpful.
G.
Hi Markus,
I found your blog as very useful. I tried reusing with my excel sheet. Unfortunately, I am unable to locate the event handler button.
Additionally, I am also finding an error when I open the excel file :
=====================
“ERROR FOUND IN CUSTOM UI XML OF :”C:\USERS\….\Addins\Eventhandler.xlam
Line: 12
Column: 45
Unknown office control id: GroupEnterDataAlignment
OK / OK TO ALL
====================
Any advise?
Under the step where you check your newly created add in, it calls it “Myaddin”. Where is that name pulled from? That string is not in your provided code, but you don’t specify what you called your filename. I have been searching for this answer online a few times the last week and can’t seem to find a way to change that display name on an existing Add in (written by my predecessor).
“myaddin” was the name of the saved file that is placed at the addin directory
Have saved the .xlam and created the customui and works great if I open excel but if excel is opened via another application the custom ribbon tab is missing. How to force all excel instances to open the customui?
Hi, you can create a function that activates the add in with a timer. If you double-click on the file, it is in some cases loaded before the addin activates.
in workbook open in the XLAM add:
Application.OnTime Now + TimeValue(“00:00:03”), “CheckIfBookOpened”
the check should validate if you have an ‘activeworkbook’ already visible on a loop, and only initialize the UI after that part is done
Works for me like a charm
Thank you very much for the tutorial 🙂
Finding the best image suited for a custom control is not that easy, as there are about 4,000 unique icons in Office 2013.
This free Excel VBA Add-in uses the Ribbon Commander framework to display dynamically built-in imageMSO icons or imported images in Excel’s Ribbon (in buttons or gallery items). What you see, is what you get in your Ribbon !
The imageMSO or any image list can be filtered using with a description keyword search or can be browsed sequentially in a ribbon gallery.
The descriptions of any buttons clicked are saved in a list, which can be exported for use with the Custom UI editor or re-imported to the Add-in for viewing.
– See more at: http://www.spreadsheet1.com/dynamic-icon-browser.html
Thank you very much. It is so helpful.
Danke schon
Pingback: Oracle Smart View Custom Excel Ribbon Part 1 | Yessbase Yessbase
HI – i am looking to launch a vba userform1 when the ribbon image is clicked. i am also getting the below error
“Cannot run the macro ‘foo_eventhandler’. The macro may not be available in this workbook or all macros may be disabled.”.
i dont know where to add the foo_eventhandler event, please help
I don’t suppose that there is anywhere that can tell me how to do this without the handy “Custom UI Editor?” I’m not able to download this at work.
Sure, the generated XML can be written by hand. The schema is public available. However, it is more error prone if you have to write it by your own.
I would like to ask how to save the file in addition to their own button
Pingback: Macro Add-in, Custom Ribbon
Thanks a lot for this post. Very informative.
Thank you for the detailed instructions. Do you know how to have the newly created ribbon appear at the end (far right) of the existing ribbons? My new ribbons are appearing on the far left.
Thank you.
Thanks for this post. It worked like a charm!