Chapter 9: Launch Macros with Events

Chapter at a Glance

In this chapter, you will learn to:

• Create custom command buttons.

• Add additional event handlers to a command button.

• Create worksheet and workbook event handlers.

My grandmother used to embroider names on outfits for us with her sewing machine. She had a powerful old sewing machine, with lots of pulleys and levers and loops. When she changed the thread, she had to poke the new thread up, over, and through countless turns, spools, and guides, before even getting to the needle. I still don’t know how she managed to embroider the names. She’d move levers and twist the fabric, and the names somehow appeared. She was very good, and the results were beautiful. She was a skilled professional.

Now even I can embroider names onto clothes. I flip the thread around a couple of guides and the sewing machine is threaded. I type in the name, select the lettering style, and push another button to embroider the name. The machine makes it easy to sew in all directions-I don’t even have to turn the fabric. Anyone can use a sewing machine these days.

Macros can simplify your life. But you don’t want the benefit of macros to be limited to skilled professionals such as yourself. If you add the buttons and controls and event handlers you’ll learn about in this chapter and the chapters that follow, you can create powerful macros that anyone can use these days.

On The CD-Important 

Before you complete this chapter, you need to install the practice files from the book’s companion CD to their default locations. See “Using the Book’s CD” on page xv for more information.

USE the Events.txt _file. This practice file is located in the Documents\MSP\ExcelVBA07SBS folder. This file contains some initial macros that you will copy into your workbook and modify during this chapter. The initial macros are stored in a simple text file so that you can be certain there is no malicious code before you put the code into a trusted location.

BE SURE TO save a blank workbook as a macro-enabled workbook named Chapter09. xlsm in the trusted location you created in Chapter 1.

OPEN the Events text file.