Protecting the Worksheet | Microsoft Excel 2002 Visual Basic for Applications Step by Step (Step by Step (Microsoft))

The model works, it doesn’t require any typing into cells, and you were able to create it without using any macros! However, the model still isn’t bulletproof. There’s nothing in the model to prevent your friend from accidentally typing Dog as the price of the car in cell C2, thereby creating errors in all the formulas.

You can protect the worksheet to prevent your friend from typing invalid values into the model. Unfortunately, that will also prevent the ActiveX controls from changing the values of the linked cells. You can, however, set the worksheet protection in such a way that Microsoft 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. Once you protect the worksheet, the direct links won’t work, so you need to create event handler procedures to put the new values into the cells.

Tip

Note that up until this point, you could keep saving the workbook as a regular workbook. You can use ActiveX controls in a standard workbook as long as they don’t have any code associated with them. As soon as you add code, you must save the workbook as a macro-enabled workbook. If you already saved the workbook as macro-enabled, you don’t have to do anything different at this time.

Turn on design mode, and select the combo box.
In the Properties window, change the Name property to cboPrice. (The prefi_xcbo stands for “combo box.”) Clear the LinkedCell property box, and then right-click the control and click View Code.

A new event handler procedure named cboPrice_Change appears. Change is the default event for a combo box.
As the body of the macro, insert this statement:
```
Range("Price").Value = cboPrice.Value 
```
This event handler procedure changes cell C2 to match the new value of the combo box whenever that value changes.
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, as it did when the LinkedCell property connected the control directly to the cell.
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("Down").Value = spnDown.Value / 100
```
Repeat Steps 1 through 4 for the scroll bar that sets the interest rate. Give it the name scrInterest, clear the LinkedCell property box, and in its event procedure enter the statement:
```
Range("Interest").Value = scrInterest.Value / 10000
```
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("Years").Value = spnYears.Value 
```
On the worksheet, 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 on 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, even with a macro or event handler procedure.

However, if you use a macro to protect the worksheet, the Protect method has a special argument that tells Excel to allow a macro or an event handler to change the protected 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. You can set the protection in the workbook’s Open event handler.

In the Visual Basic editor, click the Project Explorer button, and then double-click the ThisWorkbook object.

Project Explorer
From the Object list (above the code window), select Workbook.
Insert this statement as the body of the Workbook_Open procedure:
```
Worksheets("Loan").Protect UserInterfaceOnly:=True 
```
The UserInterfaceOnly argument to the Protect method is what allows a macro to make changes even if a user or control can’t.
Save and close the Chapter10 workbook. Then reopen it, allowing Excel to enable macros.
Try typing numbers into the model.

Excel politely explains that the worksheet is protected.
Try changing the model by using the ActiveX controls to verify that everything works correctly.

The loan payment calculator model is now robust and ready to give to your friend. Your friend can experiment with various scenarios without having to worry about typing invalid entries into the model. In fact, your friend can’t type anything into the model because the worksheet is protected. Besides, there’s actually no need 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.

CLOSE the Chapter10.xlsm workbook.