Tag Archives: CustomUI Editor

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

CustomUI Editor Part II

Add your own icons to your own ribbon

In this post i will show you how easy it is to add your own icons to your own ribbon in Microsoft Office. For this example i will use a Word document but you can just as easy make this in Excel and PowerPoint. In earlier posts i wrote about the custom UI editor. This insanly great tool for creating your own ribbons in Microsoft Applications. So that will be the tool i am using in this post. You can download the custom ui editor tool here.

The example i am creating here is a simple Word document with a custom tab with custom icons.

First make a word document which you want to use for this example. I will call it: Custom Icons. The custom UI editor tool was last updated for 2010 but to show that you can easily use this for 2013 documents i created a 2013 Word document. So the document is created, saved in a location and we are good to go. You can click on the various images to see enlargements!

Open the CustomUI editor

The next step is to open the custom ui editor. Choose open en locate your document to which you want to add a custom ribbon. For my example i created an empty Word document with no fancy stuff attached. A plain word document. Next step is to choose a type of ribbon you want to add. For this example i’ll choose a custom tab.

CUE_I CUE_II

Now with that in place you will see that de custom ui editor has presented you with some sample XML in de code window. This is the code you have to modify later on. We will first add our own icons attached to the document. For that i will add some sample icons i have stored on my pc.

CUE_III CUE_V

these are the icons i have added to my ribbon:

CUE_IV

The first time i added some icons this way i was a little startled because i did not see anything happen. You have to click the plus sign to actually see that the icons are added to the document.

CUE_VI CUE_VII

Ok, we have the document, we have the icons all we need to do now is modify the existing xml which was created when we chose custom tab at the beginning. If you are like me and you don’t want to typ to much you will use copy / paste. There’s nothing wrong with that but remember that all the buttons should have unique id’s in order for the ribbon to be created. So copy paste and rewrite the id’s for every button. Als modify the imageMSO part. imageMSO is an internal reference to existing icons from Microsoft Office. Because we are using our own icons you have to reset this option to image=”iconname here” (where iconname here is the name of your custom icon!).

CUE_VIII

The last step we have to do is to check if our ribbon will actual show up in our document. For the sample i have set two icons to large and three of them to normal. This will give you the next output:

CUE_IX

Yep there it is, my very own tab in my ribbon with my very own icons! Wow how cool is that. All done with the custom ui editor!


Pitfalls
Is there anything else i should know before i start taking these steps or will it always work. The only thing you have to remember is adding icons this way the only restriction you have is the size of the icons. In the sample i created here i added icons which have a size of 48X48 pixels. You will just have to test what the maximum size is for adding it to your tab. My advise? stick to 48 x 48 and smaller. That size fits perfectly!


Happy customizing 🙂