Creating a Global Toggle Button

I’ve worked through a number of odd issues with the Ribbon and VBA in this blog and in two of my books VBA for Dummies and RibbonX for Dummies. For example, the Creating a Custom Styles Group post shows how to make a gallery look correct when you move it to a custom tab. Likewise, the A Quick Method for Adding Custom Ribbon Elements post demonstrates a new technique for customizing the Ribbon using built-in Office 2010 features. Unfortunately, those posts won’t show how to add some specialty controls to your custom Ribbon, such as a toggle button, that also automatically loads when Excel opens. In fact, most people assume that any custom Ribbon element must be added to the individual worksheet or as part of an Excel add-in. This post will dispel that notion.

Before you begin this article, make sure you’ve read the Creating Global Macros in Excel 2007 and 2010 post. You need to understand things such as the location of the PERSONAL.XLSB file on your system and its purpose. Of course, the main purpose is to provide a means of creating global applications that load immediately when Excel loads.

If you try to add a toggle button directly to the PERSONAL.XLSB file, you’re going to quickly find that the process doesn’t work. I tried for hours to get the toggle button to even appear. Regular buttons work fine. You can also easily add galleries using techniques I’ve discussed before, but toggle buttons proved elusive at best.

The trick is to think outside the box. You can create an individual file that contains a toggle button without any problem. Yes, it does require that you create a customUI entry in the application either directly (by changing the file extension of the file to .ZIP and adding the correct elements) or by using an editor, such as the CustomUI Editor. This post won’t cover the basics of creating a custom tab. If you don’t have a copy of RibbonX for Dummies or VBA for Dummies, you can find this information on a number of sites, including MSDN.

Begin by creating a standard Excel workbook and saving it an Excel Macro-Enabled Workbook (.XLSM file). You’ll need to add a custom tab, group, and toggle button to your workbook. Here’s the code I’ve used for this example. It’s nothing elaborate. In fact, I purposely made it simple to ensure that the post would focus on technique.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<customUI onLoad="RibbonLoaded"
  <ribbon>
    <tabs>
    <tab id="myTab" label="My Tab">
      <group id="myGroup" label="My Group">
             <toggleButton id="SayHello"
                           label="Toggle SayHello"
                           onAction="SayHello_Click"
                           getPressed="SayHello_Pressed"
                           size="large"
                           imageMso="HappyFace"/>
      </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

This toggle button has two events it must handle: onAction and getPressed. In addition, you must load the Ribbon before you can make anything work. To make the button functional, I needed to add the following macros and global variables to the workbook.

' Define a global variable to hold the Ribbon reference.
Dim Rib As IRibbonUI
 
' Determines the behavior button state.
Dim lBehavior As Boolean
 
' Callback for customUI.onLoad
Sub RibbonLoaded(ribbon As IRibbonUI)
 
    ' Save the ribbon reference.
    Set Rib = ribbon
     
    ' Initialize the behavior state.
    lBehavior = False
     
    ' Show a status message.
    MsgBox "Ribbon is Loaded."
End Sub
 
' Callback for SayHello onAction
Sub SayHello_Click(control As IRibbonControl, pressed As Boolean)
 
    ' Change the behavior state.
    lBehavior = pressed
     
    ' Update the control.
    Rib.InvalidateControl (control.ID)
     
    ' Display the status.
    If pressed Then
        MsgBox "The toggle button is pressed."
    Else
        MsgBox "The toggle button isn't pressed."
    End If
End Sub
 
' Callback for SayHello getPressed
Sub SayHello_Pressed(control As IRibbonControl, ByRef returnedVal)
 
    ' Return the current behavior state.
    returnedVal = lBehavior
End Sub

Don’t forget to make sure you define the relationships if you’re editing the file by hand. OK, at this point you have a toggle button that’s functional. If you save this file, close it, and then reopen it, you’ll find that you can click the button and it’ll tell you its state—either pressed or not pressed. That’s not really the big deal here.

Now, save your file as an Excel Binary Workbook (.XLSB file). Rename this file PERSONAL.XLSB and copy it into your XLSTART folder (such as, C:\Users\John\AppData\Roaming\Microsoft\Excel\XLSTART on my system). You’ll suddenly find that you have a global toggle button. It loads every time Excel loads and the macros work just as you’d expect. Please let me know if you have any questions about this technique at John@JohnMuellerBooks.com.

Author: John

John Mueller is a freelance author and technical editor. He has writing in his blood, having produced 99 books and over 600 articles to date. The topics range from networking to artificial intelligence and from database management to heads-down programming. Some of his current books include a Web security book, discussions of how to manage big data using data science, a Windows command -line reference, and a book that shows how to build your own custom PC. His technical editing skills have helped over more than 67 authors refine the content of their manuscripts. John has provided technical editing services to both Data Based Advisor and Coast Compute magazines. He has also contributed articles to magazines such as Software Quality Connection, DevSource, InformIT, SQL Server Professional, Visual C++ Developer, Hard Core Visual Basic, asp.netPRO, Software Test and Performance, and Visual Basic Developer. Be sure to read John’s blog at http://blog.johnmuellerbooks.com/. When John isn’t working at the computer, you can find him outside in the garden, cutting wood, or generally enjoying nature. John also likes making wine and knitting. When not occupied with anything else, he makes glycerin soap and candles, which comes in handy for gift baskets. You can reach John on the Internet at John@JohnMuellerBooks.com. John is also setting up a website at http://www.johnmuellerbooks.com/. Feel free to take a look and make suggestions on how he can improve it.