Chapter 10: Use Dialog Box Controls on a Worksheet

Chapter at a Glance

In this chapter, you will learn to:

• Add ActiveX controls to a worksheet.

• Link the value of a control to a worksheet cell.

• Link a list box to a worksheet range.

• Create a list box with multiple columns.

• Protect a worksheet that uses ActiveX controls.

Microsoft Office Excel 2007 is a great program. Many people purchase it to use at work. At least, people say they’re going to use it at work. Of course, we all know the real reason most of us buy it is to calculate car payments. (The rest of us buy it to figure out mortgage payments.) It’s after buying it that we discover that it’s also good for one or two other projects as well.

Anyway, suppose you have a friend who just bought Excel but doesn’t know how to use it very well yet. You want to help out by building a model your friend can use for calculating car loan payments. You want your friend to be able to try several possible prices, interest rates, and repayment periods, but you want to minimize the chance for mistakes. Excel has some powerful tools to help you do just that.

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 Loan.xlsx workbook. This practice file is located in the Documents\MSP\ ExcelVBA10SBS folder.

BE SURE TO save a macro-enabled copy of Loan.xlsx _as Chapter10.xlsm in the trusted folder you created in Chapter 1.

OPEN the Chapter10.xlsm workbook.

Important 

ActiveX controls do not require a macro-enabled workbook unless you add event handlers to the controls. You will add event handlers in the section titled “Protecting the Worksheet” at the end of this chapter. For information about ActiveX controls and event handlers, see Chapter 9, “ Launch Macros with Events.”