<

Intercepting Office Ribbon Control Events with VBA using "Repurposing" Commands

Published on
8,616 Points
4,616 Views
5 Endorsements
Last Modified:
Jamie Garroch
Eating, sleeping, breathing PowerPoint and VBA at YOUpresent.co.uk
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Whilst the question related to PowerPoint and the VBA programming environment, the techniques in this article are equally relevant for any Microsoft Office application that supports ribbon extensibility and other add-in supported languages such as VB and C. That includes for example Excel, PowerPoint, Word and probably others in the Office suite that I not yet written add-ins for.
 

Terminology

When looking for a solution I initially tried searching for "control events", "intercepting ribbon controls" and many other variations. If I'd known that the technique is called "repurposing" by Microsoft, I'd have saved a lot of time!
So, what is repurposing?
Repurposing provides developers with a mechanism to hook into the onAction events for built-in controls. Once the solution is developed, you can take control of various buttons (and by inference, the shortcuts that trigger the underlying function) and decide what to do with it, adding your own functions and even cancelling the default MSO action.
In this example, we're going to hook into the file save function.
 

Implementing Repurposing


Prerequisites

To develop a solution, you're going to need the following:
1. Your chosen target Office application. I'll be using PowerPoint 2013.
2. The free Custom UI Editor for Microsoft Office
http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2009/08/06/7293.aspx
3. A list of Office control IDs for the Office application (and version of it) that you are targeting. Here's the link for 2010:
https://www.microsoft.com/en-gb/download/details.aspx?id=6627

Step 1 - Creating Your Project

The first thing to do is to create a new Office document and save it as a macro-enabled file. For PowerPoint, this is a .pptm or .potm file, Excel is .xlsm, Word is probably going to be .dotm etc.

Step 2 - Add the VBA Code


  1. To add the code to your project, open the VBE (Visual Basic Editor) by pressing Alt+F11
  2. Right-click on the VBAProject item you see in the top left of the window and select Insert / Module
    VBE-Insert-Module.png
  3. In the code module window that appears, paste the code I've included after step 4 below:
    VBE-Module-Code.pngThis sub will run when the user invokes the File Save command, wait for the user to click a message and then continue with the save (unless CancelDefault is set to True in which case the save will be cancelled).
  4. Save and close the file (this is very important as you cannot edit the XML while the file is open in the Office app).
 
Option Explicit

Sub myFileSave(control As IRibbonControl, ByRef CancelDefault)
  ' Pause execution and wait for the user
  MsgBox "I'm waiting for you to click OK before saving!", vbOKOnly, "Command Repurposing Demo"
  ' Continue the save
  CancelDefault = False
End Sub

Open in new window

It's useful to point out a couple of things at this stage.
Firstly, not all commands can be repurposed. Only 'simple' commands such as buttons and toggle buttons support repurposing.
Secondly, the callback signature for repurposed commands is different from the normal callback signature you would use for custom controls you add to your ribbon. This MSDN article has the details:
https://msdn.microsoft.com/en-us/library/aa722523(v=office.12).aspx
The same article also describes "There are a set of Ribbon controls whose published control IDs are not the same as control IDs that should be used for their disabling/repurposing."
Finally, if you do some online reading, you may find this definition of the callback signature which includes a type declaration for the CancelDefault variable:
 
Sub OnAction(control As IRibbonControl, ByRef CancelDefault As Boolean)

Open in new window

If you include the As Boolean declaration, the macro will fail to run and you'll see the very familiar and highly intuitive error message "The macro cannot be found or has been disabled because of your security settings." which is obviously not true! So make sure you use the correct signature:
 
Sub OnAction(control As IRibbonControl, ByRef CancelDefault)

Open in new window

 

Step 3 - Add XML Repurpose Command

To add the required XML to the project, we use the Custom UI Editor for Microsoft Office. Once installed, run it and you'll see a window like this:
Custom-UI-Editor-for-Microsoft-Offic.pngOpen your macro-enabled Office file.
Click the Insert button and select Office 2007 Custom UI Part
Next, paste the following XML into the code window:
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <commands>
    <command idMso="FileSave" onAction="myFileSave" />
  </commands>
</customUI>

Open in new window

Custom-UI-Editor-XML-Code.pngClick the tick icon in the toolbar to check that your XML is well formed.
Save the file and close it (important once again not to have it open in two places in parallel otherwise it's possible to corrupt the project).

Testing

Before testing, make sure that you have set the right security settings to enable macros to run. In Office 2013 apps, this can be found in File / Options / Trust Centre / Trust Center Settings / Macro Settings. I usually have it set to "Disable all macros except digitally signed macros" but if you don't have the ability to sign a project, select the fourth option "Enable all macros...)
Your test project is finished now so open your Office file as normal and do each of the following to see that the FileSave command has been intercepted:
1. Click the save icon in the top left of the window, next to the QAT (Quick Access Toolbar)
2. Press Ctrl+S
3. Click File to open the backstage and then press the Save button
In each case, you'll see the following message, which of course you can replace with whatever code you need:

I-m-waiting.png


Additional XML Attributes

In addition to setting the onAction attribute for built-in commands, you can also disable it permanently or dynamically using the enabled attribute and getEnable callback respectively. This is the complete list of attributes supported by commands that may be repurposed:
- enabled
- getEnabled
- idMso
- onAction


Conclusion

Microsoft have been very generous in providing a way to intercept and change the default behaviour of their products which is very powerful when developing macros and add-ins for PC-based versions of Office (we wait for PowerPoint:mac 2016 to support XML extensibility). If used carefully, you can enhance the user experience by adding your own custom functionality. Use it poorly and your users will get confused and possibly even annoyed. So use repurposing with care.
5
Comment
3 Comments
LVL 53

Expert Comment

by:Martin Liss
Go ahead and change it and tell me via a Private Editor Comment when you're done.
0
LVL 18

Expert Comment

by:Kyle Santos
Great job!
0
LVL 14

Author Comment

by:Jamie Garroch
Thanks Kyle Santos :-)
0

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Join & Write a Comment

Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Learn the basics of Skype For Business in office 365

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month