Previously I did some posts about the CustomUI editor. This nifty little tool makes it easy to create your own ribbon in the various Office applications. We have seen how to add your own ribbon, add icons to your ribbon and how to add a gallery to your ribbon. In this post I will show you how you can add a ribbon, a button and how you can assign an action to your button. The example will show a userform in Excel when you click the button in your own customized ribbon. As always I will describe the steps to achieve the result we want. So let’s do this!
First things first. Startup Microsoft Excel. It is important to know that there is a distinction in versions one might use but I will point those out to you. The example I made uses Excel 2010 (and beyond) but it is possible to use Excel 2007 as well.
Blank document Save as
The first step we need to do is create a blank document in Excel and save it as ‘Macro enabled’ workbook. Why? Because we want to assign a macro to the button we will create in our ribbon and files which contains macro’s are mandatory to save as macro enabled workbook!
Ok with the blank Excel workbook saved we can now open the CustomUI editor and start our journey. We will start from scratch. Choose open file and browse to the file you just created in Excel and click ‘Open’. Your Excel document will appear in the treeview. Now click on Insert from the menu bar.
Important: this is where the distinction between versions is made. So if you are using Office 2007 choose the second option from the drop down menu otherwise follow the example.
If you have selected one of the two options you will see an additional node appear in the treeview. You should see a little icon with the word custom next to it. Ok we are doing great so far. Now because we don’t want to reinvent the wheel all the time we are going to use some default XML code. Again go to the Insert option from the menu bar and from the drop down choose ‘Sample XML’ and choose ‘Custom Tab’ from the next menu.
Now with that in place we can do the necessary tuning to make it ours. The default sample XML has all the elements we need. It will create the ribbon, group and button we are looking for. But because it is a sample we need to modify it to our needs. In the picture below I ‘boxed’ the part we need to modify:
In the picture below you will see the modifications i have made to the existing XML to make it our own. I’ll describe the changes I have made.
- Changed the tab id: this is important because the tab id should be unique. This can be anything you’d like to call it. I called it ‘EITID’ which is the abbreviation for ExcelImportToolID
- Changed the group id: this is important because the group id also has to be unique. can you explain why I chose ‘grpEIT’ ?
- Changed the button id. You know by now that this is important so make it unique. You might notice that I keep the names in sync with the other ID’s.
- Make sure the imageMso has a capital M because otherwise it won’t show the icon!
- The final piece is the onAction bit. This is where I attach my macro reference.
Save, save, save
Of course you need to save from time to time so now would be a good moment! Let’s continue to the next step. Let us see if this works so far. Open Excel and open your document you’ve just created. It should contain a ribbon with just one button. it should look like this:
Have the same output? Ok, just remember it does not do anything yet because the macro is not there yet. So if you click the button you would get an error!
In the Excel file i created a userform (doesn’t have to be that fancy a blank userform will do). If you want to follow this example make sure the userform is called ‘frmImport’. Click insert – Userform from the menu bar and create your userform. Save your Excel file and we are ready for the next step. Adding a ‘callback’ from the customui editor.
Back to the CustomUI
Open the customui editor again and open your Excel file. You should see the XML code you have created earlier. Now to generate the callback for Excel click on the last button in the menu bar as shown below: You will see that the custom ui editor will generate some callback code for you. Copy that piece of code and after that close the custom ui editor.
Open the Excel file again in Microsoft Excel and go to the VBE (Visual Basic Editor). I added a separate module for clarity. You can do so by clicking: Insert – Module from the menu bar within the VBA environment. When done you should see a clean module. Paste your code from the custom ui editor here:
The final step we have to do is add the actual action between the lines the custom ui editor created for us. In this case we want to show the userform when the button is clicked. So all we have to do is add the line: frmImport,show between the lines that are already there. Be curious… test your ribbon callback by clicking on your button in your custom ribbon!
Taddddaaa! there it is! Well done. Now nothing is keeping you from adding more buttons and adding more macro’s to your customized ribbon. So go ahead and feel free to add more buttons and more actions by simply expanding you xml using the customui and expanding your vba module with the generated code you just have to paste and copy. Let me know how it goes 🙂