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