CustomUI Editor: Trigger Your Own Macro


 

Custom_UI_ButtonPreviously 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!

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

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

Custom_UI_EIT_IINow 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:

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

Custom_UI_EIT_IV

  1. 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
  2. Changed the group id: this is important because the group id also has to be unique. can you explain why I chose ‘grpEIT’ ?
  3. 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.
  4. Make sure the imageMso has a capital M because otherwise it won’t show the icon!
  5. 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:

Custom_UI_EIT_VHave 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!

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

Custom_UI_EIT_VI

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

Custom_UI_EIT_IX

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:

Custom_UI_EIT_VII

Last step:
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!

Custom_UI_EIT_XTaddddaaa! 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 🙂

Advertisements

7 thoughts on “CustomUI Editor: Trigger Your Own Macro

  1. Dan Judd

    I am just starting out changing the ribbon on Excel. I have much success in changing the ribbon, but then when I save using the editor, the excel workbook defaults to an empty workbook but with the new ribbon. How can I keep the populated workbook with macros and all but only change the ribbon?

    Reply
  2. Maurice Post author

    Hi Dan,
    Good question. If you want to add the ribbon to your workbook with macro’s open that file in your CustomUI editor. Add all you want to add and just click save in the CustomUI editor. Your xml will be saved with your Excel file. When you open your file you will see your ribbon with that specific file. Let me know if that helps.

    Reply
  3. Rampradeep

    Thank you for information. and now i want to add custom tab to outlook is it possible by using customUI editor.If it is possible please tell me the way like you said for Excel and word.Thank you in advance.

    Reply
    1. Maurice Post author

      Hi,
      Thank you for your feedback. You use a different toom to create your own ribbons for Outlook. You can use Visual Studio for Office Tools for that job (VSTO). Outlook doen’t have a spceific file to point to to create a ribbon the way we can in Word and Excel. Like in Access this takes a whole different approach. So in short: no you can not use the CustomUI to create a ribbon in Outlook.

      Reply
  4. Madhes

    Thanks Maurice for the detailed article.
    I would like to add custom tab/ribbon in Word (ie., available for all documents), not just one document.
    I could try “Customize the Ribbon” option, but I could not use custom icons. Any suggestions?

    Reply
    1. Maurice Post author

      Hi Madhes,
      Thank you for your feedback. If you want your ribbon to be available for all documents then you would have to add the ribbon to your normal.dotm template and deploy that normal.dotm template to your users. That way everyone can use that specific ribbon. My advice however would be to deploy a seperate .dotm template and deploy that template to your users (place the template in the Word start up folder). The normal.dotm template might be subject to Office updates and you don’t want to take that chance. In case of a Word crash a new normal.dotm template will be deployed and you will lose all your hard work. So creat a separate .dotm template and deploy that one.

      Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s