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:
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:
If control.id = “customButton” Then MsgBox “Hello World!”
End Sub
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.
Ron,
I haven’t tried it myself, but knowing the stuff that he’s produced in the past, I would definitely recommend Andy’s tool for that:
http://www.andypope.info/vba/ribboneditor.htm
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.
FWIW, here are my recent experiences of DIY ribbon customization (3 articles):
http://roymacleanvba.wordpress.com/2009/12/14/office-2007-ribbon/
The Andy Pope tool looks most impressive – I’ll certainly give it a try. Thanks for the link.
/Roy
This is where I learned Ribbonx.
http://www.rondebruin.nl/tips.htm
The examples are very good.
[…] 29, 2009 VBA Projects Leave a Comment Tags: 2007, Excel, User Interface Over at Daily Dose of Excel, Juan Pablo Gonzalez posted (in a comment) a link to Andy Pope’s site, where he has a very […]
Andy’s tool is the biggest secret on the web. Amaizing! Much better then office 2010 builtin editor.
[…] Making your own “Hello World” button in Ribbon […]
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
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.
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.
rename the file extension to zip
eg. if your file is called book1.xlsm, then rename it to book1.zip
Doh! Of course, that makes perfect sense. Thanks again!