Web Site of

Ronald R. Tidd, Ph.D., CPA

Professor, Accounting, Master of Professional Accountancy
College of Business
 

 | Bio | Home | Useful Links |

Excel Computer Lab- Macro Commands

| Objectives | Learning Tasks | References |

Objectives & Outcomes

The objective of this exercise is to help you learn how to create macro commands. You can use these commands to automate the repetitive tasks that you perform in Excel. Basic macros are recorded and saved when you perform a given procedure the first time, so they do not require programming skills.

At the end of this lab, you should be able to:

  1. Record and save Excel macro commands.

  2. Create buttons and assign each to a macro command.

NOTE: Before you start this lab, check the macro command security settings in Excel. Refer to the instructions on the web page, Adjust Excel's Macro Command Security Settings.

 | Top |

Learning Tasks

 
Task Procedures - (Useful Shortcut Keystrokes)
Record and save Excel macros.
  1. Right click here and download the assigned Excel file to your your network drive (or portable media).
  2. Open the file.
  3. Select the cell in the top left corner of the database.
  4. On the Excel menu bar, click on ToolsÞMacroÞRecord Macro.
  5. In the Record Macro dialog box:
    -    enter a name (Sort_LastName)
    -select to store the macro in This Workbook
    -add any description that you believe is necessary
    -click OK.
  6. After the dialog box closes, select the entire database:
    -hold down the CTRL key, then press the → key
    -    hold down the CTRL key, then press the →     key.
  7. On the menu bar, click on DataÞSort.
  8. In the Sort dialog box:
    -select Sort by, Last Name
    -click     OK.
  9. On the Excel menu bar, click on
    Tools    ÞMacroÞ    Stop Recording.
  10. Save your worksheet.
  11. Repeat steps 3-10, but prepare a macro to sort by percentage.
 
Create buttons and assign them to the macros.
  1. Right click on the Excel menu bar and select the Forms toolbar in the dropdown list.
  2. Select the buttons icon (refer to the image to the left).
  3. Place the cursor in the upper left hand corner of the cell immediately above the cell with the Last Name column heading .
  4. Hold down the left mouse button and drag it to the lower right hand corner of that cell (refer to the image to the left).
  5. Release the mouse button.
  6. In the Assign Macro dialog box:
    -select the Sort_LastName macro
    -click on ok.
  7. Hold down the CTRL key, and left click on the button.
  8. Select the text inside the button, and type over the existing text with a meaningful name.
  9. Click on the spreadsheet outside of the button.
  10. Save your worksheet.
  11. Repeat steps 2-10 for the sort by percentage macro.

| Top |

References

 | Top | 

Ronald R. Tidd, Ph.D., CPA
509.963.2466
© 2001-2008 All rights reserved.