Excel – Macros Lesson 2 

From the Tools menu, Customize submenu, Options tab, select all options except “large icons.”

 

This assignment will teach how to create simple macros to automate repetitive tasks. We will create macros to store information, clear a form, and print a worksheet. After creating the macros, we will run the macros, using the Tools menu. Then we will use the forms toolbar to create buttons to run the macros.

 

Data used www.cob.sjsu.edu/BUS91L/Textbook/DeliveryFormv2.xls

DO NOT OPEN THE FILE. Save the file on your desktop. Then open the file.

 

If the security setting on your browser, or in Excel, is set to high, you may not be able to download or open the file. You can view those settings on most browsers from the Tools menu, Internet Options submenu, on the Security tab. Depending on your user privileges, you may or may not be able to change those settings. In Excel you can view the settings from the Tools menu, Macro Submenu, Security submenu. Lower the security levels and then try again.

   

Tasks:

Create the Macros

 

Macro 1 Print the Invoice

  1. Click on the tab to select the Invoice Worksheet.
  2. Fill out the invoice form with imaginary data.
  3. From the Tools Menu, Macro Submenu, select Record New Macro.
  4. In the Macro Name box, type: PrintInvoice
  5. In the Description Box, Click at the end of the description. Press Enter. Type: This macro prints the delivery invoice.
  6. Click OK. The Record Macro Toolbar opens. The square blue icon is the stop button.
  7. Click on the tab to select the Invoice Worksheet. Click on cell A1.
  8. Highlight cells A1 to D46 with the mouse.
  9. Select the File Menu, then the Print submenu.
  10. In the “print what” area of the dialog box Click on Selection. Click on Ok.
  11. Press Escape. Click in cell A1.
  12. Click on the square blue icon to stop recording.

 

Macro 2 Save the Customer Information

  1. Click on the tab to select the Invoice Worksheet.
  2. Fill out the invoice form with imaginary data. (You should still have data in the form.)
  3. Click on the Customers tab. Make sure that data is showing in row 2, columns A to G. If not, go back and complete the Invoice form.
  4. From the Tools Menu, Macro Submenu, select Record New Macro.
  5. In the Macro Name box, type: SaveCustomerData
  6. In the Description Box, Click at the end of the description. Press Enter. Type: This macro saves customer information.
  7. Click OK. The Record Macro Toolbar opens. The square blue icon is the stop button.
  8. Click on the tab to select the Customers Worksheet. Click on cell A1.
  9. Click the number 5 to the left of the worksheet to select row 5.
  10. From the Insert menu, select Row.
  11. Use the mouse to select cells A2 to G2. From the Edit menu, select Copy.
  12. Click in cell A5. From the Edit menu, select Paste Special. A dialog box opens.
  13. In the dialog box, select paste values and number formats. Click Ok.
  14. Your new customer data should now be displayed in row 5. Press Escape. 
  15. Use the mouse to select cells A6 to G6. Click on the Format Painter icon. Use the mouse to select cells A5 to G5.
  16. Click on cell A1
  17. Click on cell A5. Click on the A-Z icon to re-alphabetize the list.
  18. Click on cell A1.
  19. Click on the square blue stop button on the Macro toolbar.

 

Macro 3 Save the Sales Data

 

  1. Click on the tab to select the Invoice Worksheet.
  2. Fill out the invoice form with imaginary data. (You should still have data in the form.)
  3. Click on the SalesData tab. Make sure that data is showing in row 2, columns A to H. If not, go back and complete the Invoice form.
  4. From the Tools Menu, Macro Submenu, select Record New Macro.
  5. In the Macro Name box, type: SaveSalesData
  6. In the Description Box, Click at the end of the description. Press Enter. Type: This macro saves sales information.
  7. Click OK. The Record Macro Toolbar opens. The square blue icon is the stop button.
  8. Click on the tab to select the SalesData worksheet. Click on cell A1.
  9. Click the number 5 to the left of the worksheet to select row 5.
  10. From the Insert menu, select Row.
  11. Use the mouse to select cells A2 to H2. From the Edit menu, select Copy.
  12. Click in cell A5. From the Edit menu, select Paste Special. A dialog box opens.
  13. In the dialog box, select paste values and number formats. Click Ok.
  14. Your new customer data should now be displayed in row 5. Press Escape.
  15. Use the mouse to select cells A6 to H6. Click on the Format Painter icon. Use the mouse to select cells A5 to H5.
  16. Click on cell A1
  17. Click on the square blue stop button on the Macro toolbar.

Macro 4 Clear the Inventory Form

 

  1. From the Tools Menu, Macro Submenu, select Record New Macro.
  2. In the Macro Name box, type: ClearForm
  3. In the Description Box, Click at the end of the description. Press Enter. Type: This macro clears the invoice form. 
  4. Click OK. The Record Macro Toolbar opens. The square blue icon is the stop button.
  5. Click on the tab to select the Invoice worksheet. Click on cell A1.
  6. Use the mouse to select cells B4 to B10. Press the Delete Key.
  7. Use the mouse to select cells A13 to B21. Press the Delete Key
  8. Click in cell A32. Press the Delete Key.
  9. Click in cell A1.
  10. Click on the square blue stop button on the Macro toolbar.

 

Save the file on your desktop. Use the filename InvoiceWithMacros

 

Test the Macros

  1. Click on the tab to select the Invoice Worksheet.
  2. Fill out the invoice form with imaginary data.
  3. From the Tools Menu, Macro Submenu, select Macros.
  4. A dialog Box opens. Select PrintInvoice
  5. Click on Run. Check to see if the macro worked the way you wanted.
  6. From the Tools Menu, Macro Submenu, select Macros.
  7. Select SaveCustomerData
  8. Click on Run. Check to see if the macro worked the way you wanted.
  9. From the Tools Menu, Macro Submenu, select Macros.
  10. Select SaveSalesData
  11. Click on Run. Check to see if the macro worked the way you wanted.
  12. From the Tools Menu, Macro Submenu, select Macros.
  13. Select ClearForm
  14. Click on Run. Check to see if the macro worked the way you wanted.

If a macro doesn’t work, delete it. From the Tools menu, Macro submenu, Macros submenu dialog box, select the defective macro. Click on Delete. Rebuild the macro. Macros can be edited using Visual Basic. This was covered in Macros Lesson 1.

Add Command buttons

 

  1. Open the Forms toolbar.
  2. Position the cursor in Sheet1
  3. Click on the Button command on the forms toolbar.
  4. Draw the button so it covers 4 cells in a 2x2 grid.
  5. When you release the mouse, the Assign Macro dialog box should open. Click on the name of one of the macros. Click OK.
  6. Edit the text on the button to match the name of your macro.
  7. Test the button by clicking on it.
  8. Repeat for the other three macros.

 

Save the file on your desktop. Use the filename InvoiceWithMacros.

That’s it. You’re done! Go here for the next lesson  Macros Lesson 3 or return to Macros Lesson 1