PAUSE & PRACTICE: EXCEL 7-2

PAUSE & PRACTICE: EXCEL 7-2

In this project, you edit your template to add an option button and a combo box control, unlock cells, and set worksheet protection. You then create a workbook from the template.

File Needed: [your initials] PP E7-1Template.xltx

Completed Project File Name: [your initials] PP E7-2Template.xltx and [your initials] PP E7-2Workbook.xlsx

1. Open a template for editing.
   1. Click the File tab and select Open.
   2. Click Recent and scan the file list for your template in the Custom Office Templates folder or click Folders and scan the folders list for Custom Office Templates.
   3. Click the folder name to display its contents.
   4. Select [your initials] PP E7-1Template completed in Pause & Practice 7-1.
   5. Click File and choose Save As.
   6. Edit the file name to [your initials] PP E7-2Template.
   7. Confirm that the file type is Excel Template (*.xltx). If it is not, click the drop-down list and choose Excel Template.
   8. Click Save.
2. Display the Developer tab.
   1. Right-click the Ribbon and select Show Quick Access Toolbar.
   2. Click the Customize Quick Access Toolbar arrow and click More Commands to open the Excel Options dialog box.
   3. Click Customize Ribbon in the left pane.
   4. Select the Developer box in the Main Tabs group and click OK.
3. Insert an option button form control.
   1. Select the Reservations sheet tab. The formula in cell E5 displays #N/A; this will resolve as you complete the template.
   2. Click the Insert Controls button [Developer tab, Controls group].
   3. Select the Option Button (Form Control) button in the Form Controls group.
   4. Click cell H3 to place the control.
4. Format the option button control.
   1. Click the Properties button [Developer tab, Controls group].
   2. Verify that the Control tab is selected.
   3. Select the Unchecked button for Value.
   4. Select the 3-D shading box.
   5. Click OK.
   6. Point to the control, right-click, and select Edit Text from the context menu.
   7. Delete the default label.
   8. Type Returning as the new label.
   9. Click a worksheet cell to deselect the control. If you have marked either button as Checked, right-click the control and open the Format Control dialog box. Choose Unchecked on the Control tab.
5. Position and align the controls.
   1. Point to the Returning control and right-click to display selection handles.
   2. Drag the control to the right of the New control. Align it horizontally as best as you can.
   3. Point to bottom right selection handle of the Returning control.
   4. Size the frame to match the label as best as you can.
   5. Point to the New button control, press Ctrl, and click to select both controls (Figure 7-36).
   6. Click the Align button [Shape Format tab, Arrange group].
   7. Choose Align Middle.
   8. Click a worksheet cell to deselect the controls.
   9. Confirm that both controls are unchecked (refer to step 4i).
6. Insert a combo box form control.
   1. Click the Insert Controls button [Developer tab, Controls group].
   2. Click the Combo Box (Form Control) button.
   3. Draw a control to cover cells H2:I2 (Figure 7-37).
   4. Right-click any border of the control and choose Format Control. If the command is not listed, click a cell to deselect the control and right-click the control again.
   5. Click the Input range box, click the Lists worksheet tab, select cells C1:C24, and make the references absolute.
   6. Click the Cell link box and select cell B5. The selection made in the combo box will display in this cell.
   7. Select the 3-D shading box (Figure 7-38).
   8. Click OK. Click cell I3 to deselect the combo box control.
   9. Click the combo box arrow and choose Forestview Suite. The label displays in the control and 3 displays in the linked cell (B5) because Forestview Suite is the third name in the input range.
7. Edit the control and use INDEX to display the name from the control.
   1. Right-click the combo box control and choose Format Control.
   2. Click the Cell link box and delete the reference to cell $B$5.
   3. Type i2 as the Cell link address. You cannot select the cell because it is under the combo box control.
   4. Click OK.
   5. Select cell B5 and press Delete. The rental name from the Cell link result should display here.
   6. Click the Lookup & Reference button [Formulas tab, Function Library group].
   7. Choose Index and select the array, row_num, column_num argument group.
   8. Click the Array box, click the Lists sheet tab, and select cells C1:C24. The Array for the INDEX function must be the same as the Input range for the combo box control.
   9. Click the Row_num box, type i2, and click OK (Figure 7-39). The rental name displays in cell B5 and in the combo box.
   10. Select cell E5. The XLOOKUP function displays the rental rate from the Rentals sheet by looking up the value in cell B5.
8. Unlock cells and form controls. All cells and form controls are locked by default.
   1. Select cells B7:C7. The guest’s name is entered in cell B7 but may spill over.
   2. Press Ctrl and select cells B9:C9, B11:C11, E11, B13, and B15 (Figure 7-40).
   3. Click the Format button [Home tab, Cells group] and select Lock Cell to unlock the cells. The formulas in cells B5 and E5 remain locked.
   4. Point to the combo box control and right-click. Cell I2 under the control must also be unlocked.
   5. Click the Name Box, type i2, and press Enter.
   6. Click the Format button [Home tab, Cells group], and select Lock Cell.
   7. Point to the New option button control, right-click to select it, and choose Format Control.
   8. Select the Protection tab and deselect the Locked box (Figure 7-41).
   9. Click OK.
   10. Repeat steps h–j for the Returning option button, the Yes check box, and the No check box.
9. Protect a sheet.
   1. Select cell I1.
   2. Click the Protect Sheet button [Review tab, Protect group].
   3. Verify that the Select locked cells and Select unlocked cells boxes are selected.
   4. Do not assign a password.
   5. Click OK. The command button now displays Unprotect Sheet.
10. Click the Save button in the title bar to resave your template and close it.
11. Create a workbook from your template.
    1. Click the File tab and select New.
    2. Click Personal to list the templates in the Custom Office Templates folder (Figure 7-42).
    3. Click [your initials] PP E7-2Template. The new workbook has the same name followed by a number, probably the number 1.
    4. Click the Save button in the title bar The Save this file dialog box opens.
    5. Click the Location drop-down list and choose your usual folder for saving files. If it is not listed, click More options to navigate to your folder.
    6. Edit the file name to [your initials] PP E7-2Workbook and click Save.
    7. Complete the form as shown in Figure 7-43. Use the TODAY function in cell B13 and a formula in cell B15 to add ten days to today’s date.
    8. Save and close the workbook.
12. Move the template(s) from the default folder. (Templates remain in the default folder until you delete or move them. You can also delete or move [your initials] PP E7-1Template at this time.)
    1. Click the File tab and choose Open.
    2. Click Browse to display the Open dialog box. To cut and paste (move) the file, you must use an Explorer-type dialog box.
    3. Expand the Quick access list in the left pane and select Documents.
    4. Double-click Custom Office Templates in the pane on the right to display the contents.
    5. Right-click [your initials] PP E7-2Template and choose Cut. (You can also Copy your template if instructed to do so.)
    6. Navigate to and open your folder for saving files.
    7. Right-click an unused area of the Open dialog box and choose Paste.
    8. Click Cancel to close the dialog box.
13. Hide the Developer tab and the Quick Access toolbar
    1. Click the File tab if necessary and choose Options.
    2. Click Customize Ribbon in the left pane and deselect the Developer box in the Main Tabs group.
    3. Click Quick Access Toolbar in the left pane.
    4. Deselect the Show Quick Access Toolbar box.
    5. Click OK.