Enable/Disable Controls on an Office CustomUI Ribbon with VBA

When you develop a custom Office application for a large audience you may find it necessary to limit the custom ribbon’s menu items. Using VBA you can easily add conditional “switches” to disable and enable them. When the controls are disabled they take on the standard Windows “greyed out” appearance. I have done this to hide features before an application was paid for and registered and also because some users do not need to have the disabled features. For whatever reason, doing this can make your work look more professional.

If you have already created your custom Office ribbon, this process is very simple. You simply need to add a short bit of XML to the ribbon code and add another VBA Sub procedure. This can all be done in 20 minutes or less…

Edit the XML

First, you need to open the custom ribbon XML for editing. I use the Office RibbonX Editor which I wrote about in this post. This process also works with Context menus and Dialog Box Launcher, but not control Groups. In the following screen capture, you see the 3 buttons contained within the “About” group (tab.) The first button, btnUnprotect is the one to which we want to add disabling/enabling code. To do that the XML getEnabled=”getEnabled” is added to the line which contains its attributes. This code directs the XML to a sub procedure named getEnabled for the enabled status of the control. For now, that’s enough XML editing, save the file, close the editor and open the office document.

The VBA

Lastly, you need to add a Sub procedure as shown in the following image. The Case Select code is what you use to “select” which buttons (controls) to modify. I place this type of subprocedure and all of my custom menu code in their own module, for good housekeeping.

Sub getEnabled(control As IRibbonControl, ByRef enabled As Variant)
' Sets the enabled or disabled state of the item

    Select Case control.ID
        Case "btnImport"
            enabled = g_bolLicensed
        Case "btnUnprotect"
            enabled = g_bolLicensed
        Case "btnBackup"
            enabled = g_bolLicensed
        Case "btnContract"
            enabled = g_bolLicensed
    End Select
    
End Sub

The displayed code commands that the selected controls are enabled when my global variable g_bolLicensed is True. The enabled attribute is boolean so making it equal to a boolean variable works well. You can also place conditional statements, such as If Then, within each Case clause. This code is read as your ribbon initializes when the application, such as a workbook first opens.

You can also reinitialize the ribbon when the Case statement needs to be re-evaluated because of changes to ta variable or other conditions. To do this use the Invalidate method of the toolbar. I use:

' Reset the ribbon controls
estRibbonUI.Invalidate

That refreshes the toolbar, reads the getEnabled sub and set your controls to the desire state.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.