Classes: CommandBarButton Events

In class modules, you can hook into the events of CommandBarButtons. There are a couple of times when this is an advantage. First, if you are programming the CommandBars in the VBE, the controls will not respond to the OnAction property. You have to use the class/events technique described here. Second, sometimes you need to monitor what the user is doing, but there are no events that fit the bill. In this case, you can run a sub whenever the user clicks a certain CommandBarButton (which may or may not tell you that the user has done something.)

In a previous post, I presented a user defined function to sum cells based on thier color, called SumReds. One problem with this UDF is that it doesn’t recalculate when a user changes the cell’s color. Also, there is no event to tell you that the user has changed a cell’s color. There is an event, however, that tells you when the user clicks on the Format>Cells menu item. Let’s see how we can use that to make SumReds work better.

Create a class module named Class1 and put this code in it.

The WithEvents keyword is used to expose the events of whatever object your variable is. In this case, it’s a CommandBarButton. The Click event calls another sub that we’ll create later. OnTime is used because we want this other sub to run after the default action of the CommandBarButton.

I our case, we’ll be trapping the Format > Cells CommandBarButton. We want the user to do whatever they’re going to do in the Format Cells dialog (like change the interior color of a cell) before our sub runs. Otherwise this custom event will run, then the default action of the CommandBarButton will run.

Note that one of the arguments of the Click event is CancelDefault. You can set this variable to True in the event sub so that your event takes over the CommandBarButton entirely.

Next, create a standard module with a global array variable

This will hold the two class instances that we’re going to create: One instance for the Format > Cells menu item and one for the Format Cells menu item on the right-click menu. Next create the ForceCalc sub in the standard module

Nothing to that – it just recalcs everything.

Finally, create a sub to instantiate the classes (Man I love big words like that).

Once you run this sub, those CommandBarButtons’ events will be hooked and ForceCalc will run whenever they are clicked. This will force SumReds to display the correct information.

This demonstrates how to use events for CommandBarButtons in class modules. There are a few problems with it. First, when you use a method like this, you had better be sure you know every possible way that the user can do something. If you miss one, the user will find it and things will get wacky. Another problem is that even if you know every way to do something, there may not be anything you can do about it. In the above example, I try to trap every time the user can change the color of a cell. I know of at least one way that I haven’t accounted for, the paint can control on the formatting toolbar. This particular control is a CommandBarPopup, not a CommandBarButton. Excel doesn’t let us see the events for CommandBarPopups. The controls in this CommandBarPopup (there are three of them) are CommandBarControls. Again, we don’t get the events for these, only CommandBarButtons. So as fun as this was, the user can easily circumvent our efforts.

3 thoughts on “Classes: CommandBarButton Events

  1. Hi gordon,
    If you copy the code from the browser you will need to make a couple of changes to deal with the formatting of text.
    Replace the ” with double quotes and the … with 3 periods.

  2. thanks for the speedy reply – I’d spotted the quotes but missed the ellipsis. It now works a treat.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.