CommandBar Click

Is there a method for determining if one of the built in Excel CommandBar
buttons has been clicked? I would like to trap the click event for the various
CommandBar buttons and execute code according to which button was clicked.

Any help would be greatly appreciated.

Hello Bhofsetz,

The CommandBar object has a method for determining which btton has been selected and it is exposed through VBA. It is the accHitTest method. The way it works is the command window tracks the mouse co-ordinates as the mouse moves across the CommandBar. Each button is a Child Window. The accHitTest returns an Identifier for the child or 0 if the mouse is still on the CommandBar.

To make this work in VBA you would have to a lot of API calls. You would need to the CommandBars window handle, write a mouse hook to monitor when it was over the CommandBar of interest, get the name of the Child window (button) and call your VBA routine if the AccHitTest matched the button or buttons you are interested in.

It is possible to do. Personally, it seems like a lot of work for very little payoff. I would rethink my design if I were you. There must be a better way to achieve your objective.

Sincerely,
Leith Ross

Thanks for the reply Leith,

I was afraid that doing this would take an API call. However, doing some more
digging in the Excel VBA help I found a listing for a CommandBarButton Click event. But I haven't been able to get the example given in the Excel Help to work. I am using Excel 2000 running on Windows XP

The example from VBA help is below:

Click Event Example

The following example creates a new command bar button on the File menu of the host application that enables the user to save a workbook as a comma-separated value file. (This example works in all applications, but the context of saving as CSV is applicable to Microsoft Excel.)

Please Login or Register  to view this content.

The preceding example relies on the following code, which is stored in a class module in the VBA project.

Please Login or Register  to view this content.

Any insight as to how to get this to work would be apprecaited.

Thanks