Excel Annoyances

Chapter 8. Customization, Macro, and VBA Annoyances

The Excel interface can sometimes drive you a little nutty. There’s the “adaptive” menu system in Excel 2002 (thankfully turned off by default in Excel 2003) that guesses (usually incorrectly) what menu options you want; silly limits in the filesystem; and dialog boxes that have so many tabs, checkboxes, and fields that some third-party developers make good money creating substitutes.

Ironing out these wrinkles—putting a toolbar button where you want it, or keeping snoops out of your files—is easy once you know a few insider moves. In this chapter, you’ll learn how to customize the interface, secure your data, undo changes you made in the distant past, and create macros to automate those mind- and finger-numbing repetitive tasks. You’ll also write a little Visual Basic for Applications (VBA) code to really rework and expand Excel’s capabilities.

INTERFACE CUSTOMIZATION ANNOYANCES

EXPAND THE RECENTLY USED FILE LIST

The Annoyance:

I work with many workbooks every day, and I hate having to dig through my drives to find the files I need. The recently opened file list at the bottom of the File menu shows only the last four files I opened. How do I make it show more files?

The Fix:

Simple. Choose Tools → Options, click the General tab, and in the “Recently used file list” field set the value to 9 (the maximum number allowed). If you don’t want Excel to list recently used files at all, uncheck the “Recently used file ...