Chapter 11: Command Bars and Buttons

Overview

In previous chapters, you saw how to write code in VBA and how to run it by pressing F5 or clicking the Run icon on the toolbar. However, at some point, you will want the user to be able to run your code as a professional application, and it's unlikely they'll want to go to the code window and press F5 . Many users will not even know how to do this, and if they are busy people or not technically savvy, they may just give up on your application.

When writing professional applications, it's important to remember that the less the user has to do to interact with the application, the better. Users tend to want everything as easy as possible and do not want longwinded instructions on how to run your macro. You may not even want them to be able to access the code ‚ they could easily alter it and render it useless, or it could be a security issue that no one should be allowed to see a particular formula easily.

To address these issues, you could call your code from an event such as Workbook_Open , but then it will only run when the workbook opens up and will not allow the user to make use of all the wonderful procedures you have written. Instead, you can use the CommandBars object to set up a menu, or you can place a command button on the spreadsheet itself. By using the CommandBars object, you can create new menus as part of the Microsoft Excel structure of menus . When the user selects the Tools option on the menu, for example, you can give them an extra menu option directing them to your application code. This gives a very professional look to your program and makes it appear to be part of Excel itself.