<

Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x

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

Published on
7,473 Points
3,473 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 49

Expert Comment

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

Expert Comment

by:Kyle Santos
Great job!
0
 
LVL 12

Author Comment

by:Jamie Garroch
Thanks Kyle Santos :-)
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Join & Write a Comment

This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month