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.
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.
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
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:
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
- To add the code to your project, open the VBE (Visual Basic Editor) by pressing Alt+F11
- Right-click on the VBAProject item you see in the top left of the window and select Insert / Module
- In the code module window that appears, paste the code I've included after step 4 below:
This 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).
- Save and close the file (this is very important as you cannot edit the XML while the file is open in the Office app).
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
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:
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
Sub OnAction(control As IRibbonControl, ByRef CancelDefault As Boolean)
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)
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:
Open 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:
<command idMso="FileSave" onAction="myFileSave" />
Click 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).
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
/ 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:
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:
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.