How to create a custom ribbon Addin for Excel 2010

Creating a custom ribbon addin for Excel can be done in a few steps

  1. Develop your VBA code in a new Excel sheet, save it as Excel addin .xlam
  2. Open the addin with Custom UI Editor to create Buttons, Groups, etc. and link it with your VBA code
  3. Add the addin directory to the secure folders in excel
  4. 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”

    Activate the VBA Development Tab in Excel 2010
    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 Sub

Sub 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.

Add the Addins directory to the secure locations in Excel 2010

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)

Load your Addin when Excel starts

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)

Custom Hello World Addin for Excel 2010

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!

Many icons in Excel 2010

About these ads

Über erpcoder
Software Engineer and Dynamics AX enthusiast working in Research & Development for InsideAx

15 Responses to How to create a custom ribbon Addin for Excel 2010

  1. Giulio sagt:

    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

    • erpcoder sagt:

      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

  2. Giulio sagt:

    Thank you so much. I did it.
    This post was really helpful.

    G.

  3. Chet sagt:

    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?

  4. D. S. sagt:

    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).

  5. SMF sagt:

    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?

    • mikocs sagt:

      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

  6. steven sagt:

    Thank you very much for the tutorial :)

  7. 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

  8. Shady Mohsen sagt:

    Thank you very much. It is so helpful.

    Danke schon

  9. Pingback: Oracle Smart View Custom Excel Ribbon Part 1 | Yessbase Yessbase

  10. Rajesh sagt:

    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

  11. Russell sagt:

    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.

    • erpcoder sagt:

      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.

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ photo

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s

Folgen

Erhalte jeden neuen Beitrag in deinen Posteingang.

%d Bloggern gefällt das: