W Workshops
Introduction
The Workshops are all about being creative and thinking outside of the box. These workshops will help your right-brain soar, while making your left-brain happy; by explaining why things work the way they do. Exploring possibilities is great fun; however, always stay grounded with knowledge of how things work.
Getting and Using the Project Files
Each project in the Workshops includes a start file to help you get started with the project, and a final file to provide you with the results of the project so you can see how well you accomplished the task.
Before you can use the project files, you need to download them from the Web. You can access the files at www.perspection.com in the software downloads area. After you download the files from the Web, uncompress the files into a folder on your hard drive to which you have easy access from your Microsoft Office program.
Project 1: Creating a Drop-Down List
Skills and Tools: Create a drop-down list
Entering data in a worksheet can be tedious and repetitive. To make the job easier and get consistent accurate data, you can create a drop-down list of entries you define. To create a drop-down list, you create a list of valid entries in a single column or row without blanks, define a name, and then use the List option in the Data Validation dialog box. To enter data using a drop-down list, click the cell with the defined drop-down list, click the list arrow, and then click the entry you want.
The Project
In this project, you’ll learn how to create a drop-down list from a named range of cells for use in conditional formatting.
The Process
Open Excel 2010, open DropDown_start.xlsm, and then save it as DropDown.xlsm.
Click the Numbers tab.
Select the cell range of numbers in column A, (A1:A50).
To create a drop-down list you need a single column or row without blanks.
Click the Name box, type Numbers, and then press Enter.
Click the Form Controls Sheet tab.
Select cell B1.
Click the Data tab.
Click the Data Validation button.
Click the Settings tab.
Click the Allow list arrow, and then click List.
Click the Source box, and then type =Numbers.
Click OK.
Click the Name box, type StartNum, and then press Enter.
Select cell D2.
Repeat steps 8 through 13, and name the cell EndNum.
Select cell B1, click the drop-down list, and then select a number.
Select cell D1, click the drop-down list, and then select a number.
Click the Save button on the Quick Access Toolbar.
The Results
Finish: Compare your completed project file with the results file DropDown_results.xlsm.
Project 2: Adding a Form Control
Skills and Tools: Insert a Form Control
Form controls are objects that users can interact with to enter or manipulate data. A control is a component on a worksheet used to display information or accept user input. For example, you can add a Checkbox control to your worksheet so that users can turn an option on and off. You can select a control from the Developer tab and drag to create the control directly on your worksheet.
The Project
In this project, you’ll learn how to insert a check box form control into a worksheet to control formatting.
The Process
Open Excel 2010, open FormControl_start.xlsm, and then save it as FormControl.xlsm.
Click the Developer tab.
Click the Design Mode button (highlighted), if available.
Click the Insert button, and then click the Check Box (Form Control); located on the top row.
Drag (pointer changes to a plus sign) to draw the form control in a blank area of the worksheet.
When you point to a control in Excel, you will see the pointer cursor. To select a control, right-click the object, and then click off the menu.
Select the check box object (if necessary), select the text to the right of the check box, and then press Delete.
Drag the right-middle resize handle (circles) to the size check box control to display only the check box.
Drag the check box control to cell E1 below the text Check box status.
Right-click the check box control, and then click Format Control.
Click the Checked option.
Click the Collapse Dialog button, click cell E2, and then click the Expand Dialog button.
Select the 3-D shading check box.
Click OK.
Select the check box control (if necessary), click the Name box, type a CheckBoxSwitch, and then press Enter.
Click the Design Mode button (not highlighted), and then click a blank cell to deselect the check box control.
Click the check box to turn it on and off.
The contents of cell E2 toggles between TRUE and FALSE.
Click the Save button on the Quick Access Toolbar.
The Results
Finish: Compare your completed project file with the results file FormControl_results.xlsm.
Project 3: Adding Conditional Formatting
Skills and Tools: Conditional Formatting
You can make your worksheets more powerful by setting up conditional formatting, which lets the value of a cell determine its formatting. For example, you might want this year’s sales total to be displayed in red and italics if it’s less than last year’s total, but in green and bold if it’s more. The formatting is applied to the cell values only if the values meet the condition that you specify. Otherwise, no conditional formatting is applied to the cell values.
The Project
In this project, you’ll learn how to add conditional formatting to a named range and include form controls.
The Process
Open Excel 2010, open ConditionalFormat_start.xlsm, and then save it as ConditionalFormat.xlsm.
Select the cell range A3:A32.
Click the Home tab.
Click the Conditional Formatting button, and then click New Rules.
Click Use a formula to determine which cells to format.
Type the formula
=AND($A3>=StartNum,$A3<=EndNum, CheckBoxSwitch
Click Format.
Click the Fill tab.
Click Fill Effects.
A Click the Color 2 list arrow, and then select a color.
Click a gradient option, and then select a gradient.
Click OK to close the Fill Effects dialog box.
Click OK to close the Format dialog box.
Click the Conditional Formatting button, and then click Manage Rules.
Select the Stop If True check box to provide compatibility error checking.
Click OK.
The Results
Finish: Compare your completed project file with the results file ConditionalFormat_results.xlsm.
Project 4: Creating a VBA Script
Skills and Tools: Visual Basic for Applications script
Instead of pressing the Caps Lock key every time you want to turn capitalization on and off, you can create a VBA script to change cell contents to upper case. This can be useful when you have column data, such as two-letter US state abbreviations, in a worksheet. You can type the state abbreviation in lower case and let the VBA script change it for you.
The Project
In this project, you’ll learn how to create a VBA script to change cell contents in a designated range to uppercase.
The Process
Open Excel 2010, open CAPS_start.xlsm, and then save it as CAPS.xlsm.
Click the Developer tab.
Click the Visual Basic button.
Double-click the Sheet1(Vendors) object.
The Object windows opens.
Click the Object list arrow, and then click Worksheet.
Click the Properties list arrow, and then click Change.
Type the VBA code that appears in the following illustration:
Click the Save button on the Standard toolbar.
Click the Close button on the Visual Basic Editor window to return to Excel.
Type state abbreviations in column E. After you press Enter, cell contents changes to uppercase.
Click the Save button on the Quick Access Toolbar.
The Results
Finish: Compare your completed project file with the results file CAPS_results.xlsm.
Project 5: Creating a VBA Interface
Skills and Tools: ActiveX Controls and Visual Basic for Applications (VBA) form
An ActiveX control is a software component that adds functionality to an existing program. An ActiveX control supports a customizable, programmatic interface using VBA, which you can use to create your own functionality, such as a form. Excel includes several pre-built ActiveX controls—including a label, text box, command button, and check box—to help you create a user interface.
The Project
In this project, you’ll learn how to create a form using VBA that allows users to input information using a series of dialogs, like a wizard, and use buttons to clear information and save the worksheet.
The Process
Open Excel 2010, open VBAForm_start.xlsm, and then save it as VBAForm.xlsm.
Insert an ActiveX control.
Click the Developer tab.
Click the Design Mode button (highlighted).
Click the Insert button, and then click CommandButton (ActiveX Control).
Drag to create a button control on the worksheet.
The ActiveX control is labeled CommandButton1.
Repeat steps 4 and 5 to add CommandButton2 and CommandButton3 to the worksheet.
Set control properties.
Click the CommandButton1 control to select it on the worksheet.
Click the Properties button.
In the Properties window, change the Caption text to Gather Info; change the BackColor to Inactive Title Bar (click the box next to the property to display a list arrow); change the Height to 24.75; and change the Width to 81.
Click the CommandButton2 control to select it on the worksheet, change the Caption text to Clear Results, and then make the same remaining changes in step 3.
Click the CommandButton3 control to select it on the worksheet, change the Caption text to Save Results, and then make the same remaining changes in step 3.
Click the Close button in the Properties window.
Click the Page Layout tab.
Click the Align button, and then click Align Center to align center the ActiveX control buttons.
Click the Align button, and then click Distribute Vertically to distribute vertically the ActiveX control buttons.
Click the Save button on the Quick Access Toolbar.
Add VBA functionality to the control.
Click the Developer tab.
Click the Visual Basic button.
In Project Explorer, double-click the Sheet(Annuity) object, if necessary, to open the Code Window.
Click the Object box list arrow at the top of the Code Window, and then click CommandButton1.
In the Code window, type the VBA code that appears in the following illustration:
This code clears the current contents of cells B3 through B6, moves the selection out of the way to cell A7, and then assigns named cell values, such as Present_Value, the number entered using the InputBox function. The first argument in the InputBox function is the text you want in the dialog box, and the second argument is the dialog box title.
Repeat steps 4 through 6 to add VBA code for CommandButton2 and CommandButton3 to the control that appears in the following illustration:
This code creates a Save dialog box. The code uses the MsgBox function to display a dialog box when you click the button (CommandButton2). The first argument in the MsgBox function is the text you want in the dialog box, and the second argument is the type of buttons you want to use (in this case, Yes and No), and the third argument is the type of icon you want to use in the dialog box. See the table at the end of this project for different types of dialog box buttons and icons.
This code creates a dialog box to clear the current contents of cells B3 through B6.
In Project Explorer, double-click the ThisWorkbook object, if necessary, to open the Code Window.
Type the VBA code that appears in the following illustration:
This code creates a dialog box when you open the workbook, displaying text and the username of the workbook (in this case Tim Todd). This code is placed in the ThisWorkbook object and uses the procedure Sub Workbook_Open(), so it gets executed when you open the workbook.
Click the Save button on the Standard toolbar.
Click the Close button on the Visual Basic Editor window to return to Excel.
Click the Design Mode button (not highlighted).
Click the Clear Results button, and then click Yes to clear client information.
Click the Gather Info button, enter annual salary, click OK, enter interest rate, click OK, enter the term in years, click OK, enter a reinvest percentage, and then click OK.
Enter correct values. You’ll learn how to add error checking later.
Click the Save Results button, and then click Yes to save the worksheet.
Click the Save button on the Quick Access Toolbar.
Click the File tab, and then click Close.
Click the File tab, click Recent, and then click VBAForm_results on the Recent Workbooks list.
The message alert dialog box opens.
Click OK.
Click the Save button on the Quick Access Toolbar.
Add VBA conditional functionality to the control.
Click the Developer tab.
Click the Visual Basic button.
In Project Explorer, double-click the Sheet(Annuity) object, if necessary, to open the Code Window.
Click the Object box list arrow at the top of the Code Window, and then click CommandButton1.
In the Code window, change the VBA code to add error checking to the data input that appears in the following illustration:
Click the Save button on the Standard toolbar.
Click the Close button on the Visual Basic Editor window to return to Excel.
Click the Gather Info button, enter annual salary, click OK, enter interest rate, click OK, enter the term in years, click OK, enter 0 (as a reinvest percentage), re-enter a reinvestment percentage greater than 0, and then click OK.
Click the Save button on the Quick Access Toolbar.
The Results
Finish: Compare your completed project file with the presentation VBAForm_results.xlsm.
Want More Projects
You can access and download more workshop projects and related files at www.perspection.com in the software downloads area. After you download the files from the Web, uncompress the files into a folder on your hard drive to which you have easy access from your Microsoft Office program.
Get Everything on DVD
Instead of downloading everything from the Web, which can take a while depending on your Internet connection speed, you can get all the files used in this book and much more on the Microsoft Office 2010 On Demand DVD. The DVD contains task and workshop files, tips and tricks, keyboard shortcuts, transition helpers from 2003 or 2007 to 2010, and other goodies from the author.
To get the Microsoft Office 2010 On Demand DVD, go to www.perspection.com.