Using Macros, Controls, and Visual Basic for Applications ...

Using Macros, Controls, and Visual Basic for

Applications (VBA) with Excel

? Chapter Introduction ? 10-1 Project -- Waterfront Studios

o 10-1a Roadmap o 10-1b To Run Excel and Open a Workbook o 10-1c To Unprotect a Password-Protected Worksheet ? 10-2 Phase 1 -- Recording Macros and Assigning Them to a Toolbar Button and Keyboard Combination o 10-2a To Display the DEVELOPER Tab, Enable Macros, and Save a Workbook as a Macro-

Enabled Workbook o 10-2b To Convert Names to Proper Case and Add Extra Columns o 10-2c Recording Macros o 10-2d To Record a Macro to Reformat the Artist Data Where First Name Appears First o 10-2e To Record a Macro to Reformat the Artist Data Where Last Name Appears First o 10-2f To Password-Protect the Worksheet, Save the Workbook, and Close the Workbook o 10-2g Changing the Macro Security Level o 10-2h To Set the Macro Security Level o 10-2i To Open a Workbook with Macros and Execute a Macro o 10-2j To View and Print a Macro's VBA Code o 10-2k To Add a Button to the Quick Access Toolbar, Assign the Button a Macro, and Use the

Button o 10-2l To Use a Data Form to Enter Additional Records ? 10-3 Phase 2 -- Creating a Mailing List Data Entry Screen Automating the Mailing List Data Entry o 10-3a To Open a Workbook and Unprotect a Worksheet o 10-3b To Format Name and Address Fields o 10-3c To Add Form Controls to a Worksheet o 10-3d To Group Option Buttons in a User Interface o 10-3e To Add a Command Button Control to the Worksheet o 10-3f To Add a Text Box Control to the Worksheet o 10-3g Setting Form Control Properties o 10-3h To Format the Option Button Controls for Contact Method o 10-3i To Format the Group Box Controls o 10-3j To Format the Check Box Controls o 10-3k Setting ActiveX Control Properties o 10-3l To Format the Command Button and Text Box Controls o 10-3m To Add Fill Color to the Worksheet o 10-3n To Record User Input for Contact Method to Another Location on the Worksheet o 10-3o To Record User Input for the Group Box Controls o 10-3p To Record User Input for the Check Box Controls o 10-3q To Assign Names to Cells o 10-3r Writing Code for a Command Button o 10-3s To Enter the Command Button Procedures Using the Visual Basic Editor o 10-3t To Remove the Outline from the Group Control o 10-3u To Prepare and Protect the Worksheet and Save the Workbook o 10-3v To Test the Controls in the New Member Signup Sheet o 10-3w To Reset the Quick Access Toolbar and Ribbon and Exit Excel ? 10-4 Digital Signatures ? 10-5 Chapter Review

o 10-5aChapter Summary o 10-5bApply Your Knowledge o 10-5cExtend Your Knowledge o 10-5dAnalyze, Correct, Improve o 10-5eIn the Labs o 10-5f Consider This: Your Turn o 10-5gLearn Online

?

10-1 Project -- Waterfront Studios

The project in the chapter follows proper design guidelines and uses Excel to create the worksheets shown in Figure 10?1 and Figure 10?2. Waterfront Studios is part of a mill rehabilitation project that provides work and exhibition space for artists. Waterfront Studios works with a group of visual artists. The artist board of Waterfront Studios needs to keep records of the artists who are part of the cooperative. Basic information (name, address, and the last four digits of Social Security number) was provided by the artists themselves. Some information was being maintained informally by one of the artists at a previous cooperative effort in an earlier version of Excel. Figure 10?1a shows the state of the existing artist information, and Figure 10?1b shows the final layout for the information. In addition, the studio needs to develop a way to enter new artist information in a format consistent with the current layout. The studio also needs an easy-to-use interface that will allow visitors at various studio events to provide contact information for promoting other events. The interface can assume that the user has some comfort with computers, but should not assume the user has Excel knowledge. Figure 10?2 shows the user interface that will be used at studio events and the separate worksheet that contains the information gathered through the user interface. Figure 10?1

Figure 10?2

The studio information workbooks are modified according to the following phases:

? Phase 1 -- Update the workbook to reformat the artist information in the artist data worksheet to a consistent layout through the use of macros. Use a data form to provide a user interface for entering new artist data.

? Phase 2 -- Create an area on a worksheet called Mailing List Signup (Figure 10?2) that allows a studio visitor with no Excel knowledge to enter his or her name, address, and additional information using command buttons, check boxes, and option buttons.

Figure 10?3 illustrates the requirements document for the Waterfront Studios workbooks. It includes the needs, source of data, summary of format changes, and other facts about its development. The requirements document indicates that the development of the workbooks should be done in two phases, as outlined earlier. Figure 10?4

shows a rough sketch of the user interface described in the requirements document, to assist in the initial layout of the user interface in Phase 2.

Figure 10?3

Worksheet Title

Waterfront Studios Artists and Waterfront Studios Mailing List

An easy-to-use interface for the Studios' activities as follows:

Phase 1:

Needs

Use the macro recorder to create macros that reformat the artist names in the artist data worksheet so that the last name is in column A and the first name is in column B. This will require two macros. Both macros will be assigned to a key combination and one will be assigned to a button on the Quick Access Toolbar.

Use a data form to add additional records.

Phase 2:

Source of Data

Create an area on the Mailing List Signup worksheet (Figure 10?2) that allows a patron with no Excel knowledge to enter his or her name, address, and additional information using command buttons, check boxes, and option buttons.

The workbook shown in Figures 10?1a and 10?1b is available in the Data Files for Students with the file name, Waterfront Studios Data. The workbook containing the base information for the workbook shown in Figures 10?2a and 10?2b is available in the Data Files for Students, with the file name Waterfront Studios Mailing Data.

? 2014 Cengage Learning Figure 10?4

10-1a Roadmap

In this chapter, you will learn how to modify the existing Waterfront Studios Artists workbook shown in Figure 10?1 and Figure 10?2 through the use of macros, data forms, and Visual Basic for Applications. The following roadmap identifies general activities you will perform as you progress through this chapter:

1. SET PROTECTION and SECURITY. 2. WRITE MACROS. 3. CREATE the DATA FORM. 4. DESIGN the USER INTERFACE. 5. SET the CONTROL PROPERTIES. 6. WRITE the VISUAL BASIC CODE. 7. TEST the USER INTERFACE.

At the beginning of step instructions throughout the chapter, you will see an abbreviated form of this roadmap. The abbreviated roadmap uses colors to indicate chapter progress: gray means the chapter is beyond that activity, blue means the task being shown is covered in that activity, and black means that activity is yet to be covered. For example, the following abbreviated roadmap indicates the chapter would be showing a task in the 2 WRITE MACROS activity.

Use the abbreviated roadmap as a progress guide while you read or step through the instructions in this chapter.

BTW

BTWs

For a complete list of the BTWs found in the margins of this book, visit the BTW resource on the Student Companion Site located on . For detailed instructions about accessing available resources, visit ct/studentdownload or contact your instructor for information about accessing the required files.

10-1b To Run Excel and Open a Workbook

The following steps, which assume Windows 8 is running, use the Start screen or the search box to run Excel based on a typical installation. You may need to ask your instructor how to run Excel on your computer. For a detailed example of the procedure summarized below, refer to the Office and Windows chapter.

To complete these steps, you will be required to use the Data Files for Students. Visit ct/studentdownload for detailed instructions or contact your instructor for information about accessing the required files.

1. 1

Scroll the Start screen for an Excel 2013 tile. If your Start screen contains an Excel 2013 tile, tap or click it to run Excel. Otherwise, search for the Excel app using the Charms bar and then tap or click Excel 2013 in the search results.

2. 2

Tap or click `Open Other Workbooks' on the Excel start screen.

3. 3

Navigate to the location of the Data Files for Students and the Excel Chapter 10 folder.

4. 4

Double-tap or double-click the file named Waterfront Studios Data to open it.

5. 5

If the Excel window is not maximized, tap or click the Maximize button on its title bar to maximize the window.

6. 6

Save the workbook with the file name, Waterfront Studios Artists (Figure 10?5).

Figure 10?5

BTW

The Ribbon and Screen Resolution

Excel may change how the groups and buttons within the groups appear on the ribbon, depending on the computer's screen resolution. Thus, your ribbon may look different from the ones in this book if you are using a screen resolution other than 1366 ? 768.

Consider This

How Do You Familiarize Yourself With A Workbook Created by Someone Else?

When you work with a workbook created by someone else, such as the Waterfront Studios Artists workbook, you should review the content and structure of the workbook before you make changes to it. You can learn more about a workbook by doing as much of the following as possible to the worksheet:

1. Press CTRL+ACCENT MARK (`) to display any formulas to gain an understanding of what formulas and functions are used in the worksheet and which cells are referenced by the formulas and functions.

2. Use Range Finder or the auditing commands to show which cells are referenced in formulas and functions. You double-tap or double-click a cell with a formula or function to activate Range Finder.

3. Check which cells are locked and which cells are unlocked. Usually all cells in a workbook are locked, except for those in which you enter data. For example, on the All Artists worksheet, only the cells in the range A3:G3 are unlocked.

4. Enter sample data and verify the results.

Add Bookmark to this Page

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download