Working with Command Bar Events
You can use command bar event procedures to run your own code in response to an event. In addition, you can use these event procedures to substitute your own code for the default behavior of a built-in control. The CommandBars collection and the CommandBarButton and CommandBarComboBox objects expose the following event procedures that you can use to run code in response to an event:
- The CommandBars collection supports the OnUpdate event, which is triggered in response to changes made to a Microsoft® Office document that might affect the state of any visible command bar or command bar control. For example, the OnUpdate event occurs when a user changes the selection in an Office document. You can use this event to change the availability or state of command bars or command bar controls in response to actions taken by the user.
Note The OnUpdate event can be triggered repeatedly in many different contexts. Any code you add to this event that does a lot of processing or performs a number of actions might affect the performance of your application.
- The CommandBarButton control exposes a Click event that is triggered when a user clicks a command bar button. You can use this event to run code when the user clicks a command bar button.
- The CommandBarComboBox control exposes a Change event that is triggered when a user makes a selection from a combo box control. You can use this method to take an action depending on what selection the user makes from a combo box control on a command bar.
To expose these events, you must first declare an object variable in a class module by using the WithEvents keyword. The following code, entered in the Declarations section of a class module, creates object variables representing the CommandBars collection, three command bar buttons, and a combo box control on a custom toolbar:
Public WithEvents colCBars As Office.CommandBars Public WithEvents cmdBold As Office.CommandBarButton Public WithEvents cmdItalic As Office.CommandBarButton Public WithEvents cmdUnderline As Office.CommandBarButton Public WithEvents cboFontSize As Office.CommandBarComboBox
When you use the WithEvents keyword to declare an object variable in a class module, the object appears in the Object box in the Code window, and when you select it, the object's events are available in the Procedure box. For example, if the clsCBarEvents class module contained the previous code, you could select the colCBars, cmdBold, cmdItalic, cmdUnderline, and cboFontSize objects from the Object drop-down list and each object's event procedure template would be added to your class module as follows:
Private Sub colCBars_OnUpdate() ' Insert code you want to run in response to selection changes in an ' Office document. End Sub Private Sub cmdBold_Click (ByVal Ctrl As Office.CommandBarButton, _ CancelDefault As Boolean) ' Insert code you want to run in response to this event. End Sub Private Sub cmdItalic_Click (ByVal Ctrl As Office.CommandBarButton, _ CancelDefault As Boolean) ' Insert code you want to run in response to this event. End Sub Private Sub cmdUnderline_Click (ByVal Ctrl As Office.CommandBarButton, _ CancelDefault As Boolean) ' Insert code you want to run in response to this event. End Sub Private Sub cboFontSize_Change (ByVal Ctrl As Office.CommandBarComboBox) ' Insert code you want to run when a selection is made in a combo box. End Sub
You add to the event procedures the code that you want to run when the event occurs.
Note If you set the variable for a command bar control object to a built-in command button, you can set the Click event's CancelDefault argument to True to prevent the button's default behavior from occurring. This behavior is useful if you are developing an add-in and want code to run instead of, or in addition to, the application code that runs when a built-in button is clicked.
After you have added code to the event procedures, you create an instance of the class in a standard or class module and use the Set statement to link the control events to specific command bar controls. In the following example, the InitEvents procedure is used in a standard module to link clsCBarEvents object variables to specific command bar controls on the Formatting Example toolbar:
Option Explicit Dim clsCBClass As New clsCBEvents Sub InitEvents() Dim cbrBar As Office.CommandBar Set cbrBar = CommandBars("Formatting Example") With cbrBar Set clsCBClass.cmdBold = .Controls("Bold") Set clsCBClass.cmdItalic = .Controls("Italic") Set clsCBClass.cmdUnderline = .Controls("Underline") Set clsCBClass.cboFontSize = .Controls("Set Font Size") End With Set clsCBClass.colCBars = CommandBars End Sub
When the InitEvents procedure runs, the code you placed in the command bar's and command bar controls' event procedures will run whenever the related event occurs.
Working with Command Bars | Manipulating Command Bars and Command Bar Controls with VBA Code | Getting Information About Command Bars and Controls | Creating a Command Bar | Hiding and Showing a Command Bar | Copying a Command Bar | Deleting a Command Bar | Preventing Users from Modifying Custom Command Bars | Working with Personalized Menus | Working with Images on Command Bar Buttons | Working with Command Bar Controls