61.

Protecting the Worksheet

The model works fine now. It doesn't require any typing into cells. And you were able to create it without using any macros! The model still isn't bulletproof, however. There's nothing in the model to prevent your friend from accidentally typing, say, 'Dog' in cell C2 as the price of the car, thereby destroying the formula.

You might protect the worksheet. That would keep your friend from typing invalid values into the model; but it would also unfortunately keep the ActiveX controls from changing the values of the linked cells. You can, however, set the worksheet protection in such a way that Visual Basic for Applications procedures can still change the cells. All you need are some simple event handler procedures to protect the model effectively.

Create an Event Handler for the Combo Box

Right now, each of the four ActiveX controls links directly to a cell. Before you can protect the worksheet, you must create four event handler procedures to put the new values into the cells.

1. Turn on design mode, and select the combo box.

   View Code

   

2. In the Properties window, change the Name property to cboPrice. (The prefix cbo stands for 'combo box.') Clear the LinkedCell property box, and then click the View Code button on the Control Toolbox.

   A new event handler procedure named cboPrice_Change appears. Change is the default event for a combo box.

3. As the body of the macro, insert this statement:

   Range("C2").Value = cboPrice.Value

   

   Tip 

   For more information about locking cells, activate Excel and ask the Assistant for help using the keywords locked cells.

   This event handler procedure changes cell C2 to match the new value of the combo box whenever that value changes.

4. Activate Excel, turn off design mode, and try out the combo box.

   The value in cell C2 should change to the correct price each time you select a new car.

5. Repeat steps 1 through 4 for the spin button that sets the down payment percentage. Give it the name spnDown, clear the LinkedCell property box, and in its event procedure enter the following statement:

   Range("C3").Value = spnDown.Value / 100

   

6. Repeat steps 1 through 4 for the scroll bar. Give it the name scrRate, clear the LinkedCell property box, and in its event procedure enter the statement:

   Range("C5").Value = scrRate.Value / 10000

   

7. Repeat steps 1 through 4 for the spin button that sets the number of years. Give it the name spnYears, clear the LinkedCell property box, and in its event procedure enter the statement:

   Range("C6").Value = spnYears.Value

   

8. Clear cells H3 and H5 since you no longer need the values in them.

You now have an event handler procedure for each control, and none of the controls is linked to a cell. You're finally ready to protect the worksheet.

Protect the Worksheet

You typically protect a worksheet by clicking the Protection command from the Tools menu and then clicking the Protect Sheet command. When you protect a worksheet this way, you can't subsequently change the value of any locked cells. Excel 2002 has many new options for items you can allow a user to change on a protected worksheet, but none of the standard options allow an ActiveX control to change the value in a cell while locking the cell from the user.

A macro, however, can protect a worksheet in such a way that a procedure-such as an event handler-can still change locked cells. This special kind of protection doesn't persist when you close and reopen the workbook, so you must protect the worksheet each time you open the workbook. Excel has an event that runs each time you open a workbook.

Project Explorer

1. Activate the Visual Basic Editor, click the Project Explorer button, and then double-click the ThisWorkbook object.

2. From the Object list (above the code window), select Workbook.

3. Insert this statement as the body of the Workbook_Open procedure:

   Worksheets("Loan").Protect UserInterfaceOnly:=True

   

4. The UserInterfaceOnly argument to the Protect method is what allows a macro to make changes even if a user or control can't.

5. Save and close the Chapter10 workbook. Then reopen it, allowing Excel to enable macros.

6. Try typing numbers into the model. Excel politely explains that the worksheet is protected.

   

7. Try changing the model using the ActiveX controls. Everything works fine.

The loan payment calculator model is now robust and ready to give to your friend. Your friend can now experiment with various scenarios without having to worry about typing invalid inputs into the model. In fact, your friend can't type anything into the model- because the worksheet is protected. Besides, there's nothing to type. Your friend can control everything on the worksheet just by clicking controls with the mouse. One of the greatest benefits of a graphical user interface is the ability to restrict choices to valid values, thereby reducing or eliminating user error while also making a model easier to use.