By Lisanne Balcells-Baldwin

A simple On-Off toolbar button is easy to set up. A lot of the choices on the Tools-Options menu item are ideal for this--those where you check a box as to whether you want them or not. Let's set one up for View Formulas (rather than viewing Values on a spreadsheet).

First, I'm going to assume that you want to always have this macro available. Therefore it needs to be in your personal.xls or in any other workbook that is stored in your main xlstart or alternate startup directory. Go to your worksheet, click Tools-Macro-Record New Macro. You will be prompted for a name to give the macro, and asked where you want it stored. Select Personal Macro Workbook, and give it a shortcut key if you wish (note: Excel uses a lot of Ctrl+letter shortcuts; it's safer to use Ctrl+Shift+letter so you don't overwrite something). When you click on OK, you will start the macro recorder--it will keep track of all of your actions.

Just do the one simple action: select Tools-Options, and check the View Formulas box. That's it. Then turn the recorder off by clicking on the button with the small dark blue square (this toolbar is probably floating on your worksheet, rather than being in the toolbar area at the top). Now look at what you've recorded: go to Tools-Macro-Visual Basic Editor (or Alt-F11), select your personal workbook on the left side (Project Explorer), and click on the module. The macro will probably look something like this:

Now you need to do a slight modification to the one line of code. Instead of x = True, make it x = Not x, as in:

That's all there is to the macro. The "NOT" makes it a toggle--it flips back and forth between two states.

Now go back to Excel (Alt-F11, Excel X in upper left, or click on a spreadsheet), and attach it to a toolbar button. First select View-Toolbars-Customize, then choose the Commands tab. One of the last Categories is Macros; click on it, and then grab and drag (left mouse button) a smiley face to a toolbar. If this is a button you always want, then you might dump something you don't use off of the Standard toolbar, and put this in its place. Right-click on the button, and use "Assign Macro" to attach the macro you just wrote. You can always change the button face and tooltips (right-click on the button again, and look at the options). When you're through, Close the Customize box, and you're done!

Top of Page