Hello World Button on a Ribbon

Many of my spreadsheets use Excel 2003 Commandbars for application control.
Having loaded these spreadsheets into Excel 2007, I see my commandbars presented on the Add-In tab of the Ribbon.

I’m a late starter with Excel 2007 ribbons, but I guess it’s time to move.
I’ve found that the various guides on the Internet tend to throw you in the deep end. I think this is a trend not specific to Excel. There is a real lack of guides out there for the developer who is just starting and knows nothing. Perhaps I’m just looking in all the wrong places?

My goal for the week was to, quite simply, create a button on a Ribbon that displays “Hello World” when clicked.

There are basically two parts to getting a button onto the ribbon.
1. Inject a special file into the xlsm, which you can use Custom UI Editor Tool for (look for the OfficeCustomUIEditorSetup.zip attachment).
2. Add some code into a VBA module.

Instructions as follows:
– Download and Install Custom UI Editor for Microsoft Office

From Excel:
– Create a new Excel Macro-Enabled Workbook, and save as Book1.xlsm
– Close the Workbook

From Custom UI Editor:
– Open Book1.xlsm
– From the Insert menu, select Office 2007 Custom UI Part
– From the Insert menu, select Sample XML > Custom Tab
– The Sample XML is actually tuned for Office 2010, so on the top line where it says the schema is 2009/07, change that to 2006/01.
– Save

Your screen should look as follows:
ribbonbasics_1

From Excel:
– Open Book1.xlsm (Do you notice the Custom Tab on the ribbon?)
– Alt-F11 to VBA
– Insert a Module, and insert this code:

Sub Callback(control As IRibbonControl)
    If control.id = “customButton” Then MsgBox “Hello World!”
End Sub

ribbonbasics_2

From Excel:
– Click the Custom Tab, Happy Face button.
Hello World!

Some notes…
xlsx and xlsm files are really just zip files in disguise. Rename the file extension, and you can browse the contents.
The purpose of Custom UI Editor is to add a folder containing the XML file into the xlsm. The folder is customUI, and the file is customUI.xml.
You might feel more comfortable doing this by hand but I have a feeling there are better tools out there. Being a complete newbie at ribbons, I don’t know of any, but perhaps someone will add a link in the comments.

Posted in Uncategorized

13 thoughts on “Hello World Button on a Ribbon

  1. Juan Pablo,
    Thanks for the information. I tried Andy’s add-in and found it easy to use. No xml knowledge required. I would never have ventured in customizing Excel UI without it. This is an awsome piece of code.

    Ron,
    Thanks for this helpful post.

    Merry Christmas to everyone.

  2. Andy’s tool is the biggest secret on the web. Amaizing! Much better then office 2010 builtin editor.

  3. I was able to create a custom toolbar using the instructions in the post and the Custom UI Editor.

    However, when I try to use Andy Pope’s tool it doesn’t work. I can’t create a new custom toolbar nor can I edit an existing one that I created using the Custom UI Editor from Microsoft.

    Any ideas what might be wrong?

    I’m running Excel 2007/SP2.

    OS NameMicrosoft Windows XP Professional
    Version5.1.2600 Service Pack 3 Build 2600

    Thanks in advance for any help

  4. Hi Matt,

    Rather than clog this post with a debug session drop me an email (andy AT andypope DOT info) and we can post back here with the the final outcome.

  5. Have followed your steps and found your walkthrough very helpful. Thanks!

    Rob, you mention on this site,

    “xlsx and xlsm files are really just zip files in disguise. Rename the file extension, and you can browse the contents.”

    Could you please tell me, what do rename the xlsm file extension to in order to browse the contents?

    TIA.

  6. rename the file extension to zip
    eg. if your file is called book1.xlsm, then rename it to book1.zip


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.