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
– 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.
Your screen should look as follows:
– 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!”
– Click the Custom Tab, Happy Face button.
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.