Page 321 - Excel Workbook for Dummies

33_798452 ch23.qxp 3/13/06 7:51 PM Page 304
304 Part VII: Macros and Visual Basic for Applications

3. Press Alt+F11 to open the Visual Basic Editor window with a new module sheet
from the PERSONAL.XLS workbook.
4. Choose the Tools➪VBAProject Properties menu command.
The Editor opens the VBAProject - Project Properties dialog box containing a
General and a Protection tab.
5. Click the Protection tab and then select the Lock Project for Viewing check box.
Putting a check mark in this check box prevents other users from viewing the
custom functions so that they can’t make any changes to them. Next, you add a
password that prevents them from removing the view protection status.
6. Click the Password text box and enter cerrado (Spanish for closed) as the
password there. Then click the Confirm Password text box and reenter cerrado
as the password exactly as you entered it in the text box above before you select
OK.
Now you’re ready to return to the worksheet where you can add a title and
description for the new add-in file.
7. Click the View Microsoft Excel button on Visual Basic Editor Standard toolbar.
Before saving the PERSONAL.XLS workbook as an add-in file, you should add a
title and description of the user-defined Commission function it contains (this
information then appears in the Add-Ins dialog box whenever you select the
add-in file).
8. Choose the File➪Properties menu command to open the PERSONAL.XLS
Properties dialog box.
9. Enter Custom Commission Function in the Title text box on the Summary tab
and then select the Comments text box.
10. Enter the following text in the Comments text box and then select OK:
Computes sales commissions based on the total and number of sales
11. Choose the File➪Save As menu command and then scroll all the way down to the
bottom of the Save as Type drop-down list in Save As dialog box. Click Microsoft
Office Excel Add-In (*.xla).
Doing this selects the AddIns folder in the Save In drop-down list box showing
the names of any add-in files that you’ve saved there.
12. Click the File Name combo box and change PERSONAL to Commission (without
changing the .xla filename extension) before you select the Save button.
After saving your workbook as an add-in file, you’re ready to activate the add-in
so that you can enter its user-defined functions in any worksheet.
13. Hide the Personal Macro Workbook, PERSONAL.XLS, by choosing the
Window➪Hide menu command.
14. Choose the Tools➪Add-Ins menu command to open the Add-Ins dialog box and
there select the Custom Commission Function check box before selecting OK.
15. Position the cell pointer in cell E18 and then type
=Commission(
16. Click cell C17 with Jack’s totals sales to make it the TotalSales argument for the
Commission function and then type a , (comma) to separate the TotalSales argu-
ment from the ItemsSold argument.