Useful toolbar buttons you don't see every day in Excel 2003, Part 2
September 18, 2009 • JP • No Comments • Rate This Article
In Part One of Excel Toolbars You Don't See Every Day, we looked at a few commands you may have forgotten about, which can easily be added to any toolbar in Excel 2003. Let's continue by looking at the Tools menu and the hidden gems offered there.
As a refresher, if you want to add any of these commands to a toolbar, just right click any toolbar, or go to View » Toolbars » Customize. Click on the Commands tab, select the menu Category on the left of the dialog box, then scroll down the commands on the right. Click and drag the command of your choice to the toolbar of your choice. Right-click the newly added button to customize the icon, text, accelerator, and so on.
In Part One, Jon Peltier mentioned how you can add an icon (if it's missing) by using the Copy Button Image feature (see also Change a picture on a button). I'd do that for any commands which don't have icons.
Please pass the workbook
If you publish shared workbooks for use by multiple users, first of all I want to know who you are so I know who not to work for. 
Seriously, this is a handy command to have close by, if you have a need to allow editing by multiple users simultaneously.
Macros, Macros, Macros!
There are several macro-related commands you can add to a toolbar, but here are the ones I use most frequently.
- Record New Macro…
- Visual Basic Editor
I sometimes like to record a short macro to check syntax, and of course the second command leaves the VBIDE just a click away.
Calculation
There are two calculation-related commands you may not have known existed.
- Calculate Full
- Calculate Now
These correspond roughly to the CalculateFull and Calculate methods in VBA (as far as I can tell).
Creating Outlook tasks
On the Tools Menu, there's an item called Create Microsoft Office Outlook Task.
I don't know if this is found in everyone's copy of Excel, but I assume it's installed along with Outlook/Office. But you can use it to quickly create a task in Outlook. You can also create a task silently using VBA.
Smile and say cheese!
Jon Peltier mentioned the Camera Tool in Part One. What it does is "take a picture" of the current selection and allow you to display it elsewhere on the worksheet. So you can view data in a different part of the worksheet together with other data. Changing the source data will update the snapshot.
For more information, visit The Camera Tool.
Reading cells
Speak Cells is a fun item to have on a toolbar. It reads back cell content to you, in that old-school computer voice. It actually pronounced my name correctly.
Formula auditing tools
Two commands are very useful here. I use them pretty regularly.
- Evaluate Formula
- Formula Auditing Mode
The Evaluate Formula command brings up a dialog box where you can step through the calculation of a formula in a cell. I usually use it to check a formula that returns an error, but it's useful for checking any formula cell to see how it returns its result.
Formula Auditing Mode toggles the display of formulas on the worksheet and opens the Formula Auditing toolbar. It's the same thing as pressing Ctrl+` (grave) on your keyboard.
Further Reading
For further reading, my recommendation is to start with Customized Excel Toolbars and Quickly Customize an Excel Toolbar from Debra Dalgleish's blog.
Excel ·
Rate This Article:
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: Sharing email addresses in Outlook and an approach to problem-solving
Next Post: True Performance Improvements in Excel 2010?
You should be careful about recommending Shared Workbooks. It's not exactly like multiple users on the same workbook at the same time, because some saves may overwrite other users' changes. Also it's a very fragile feature, which results in much frustration.
I definitely don't recommend it, but if you do use the feature often, you can add the button to a toolbar to make it easier to toggle.
Somewhere recently I read that a good programmer makes it easy for a user to do what should be done, and difficult to do what shouldn't be done. So by rights, a good programmer would not show how to add a button to make this easier, but how to hide the menu command that makes it possible at all.
Ouch. Okay, to remove the Share Workbooks option, just right-click the toolbar, choose Customize, then click the Tools menu, click and drag "Share Workbook" off the menu.