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.



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 ...

Get Excel Annoyances now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.