Excel Agility: Macro Basics Part 4

On Demand Webinar

Webinar Details $219

  • Rated:
  • Webinar Length: 100 Minutes
  • Guest Speaker:   David Ringstrom
  • Topic:   Business Administration, Business Skills, Software, Taxation and Accounting, Finance
  • Credit:   ATAAA 1.5, ATATX 1.5, ATAOP 1.5, CPE 2.0
All Access Membership

This is the perfect course for those who want to learn even more about Excel macros. Step-by-step, Excel expert David Ringstrom walks you through the process of creating custom dialog boxes that can add interactivity in Excel and better manage a user’s actions. He then builds a custom printing interface that he dubbed “Plug and Print,” which allows you to pick and choose the worksheets to print from any workbook. The presentation also teaches you how to create a basic UserForm for prompting users to input data, including all the required fields. Finally, you’ll see how to move module sheets and UserForms between workbooks, as well as how to remove them from workbooks when they’re no longer needed.

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. He draws your attention to any differences in Excel 2013, 2010, or 2007 during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the webcast.  

Who Would Be Interested in This Course:

Practitioners seeking to incorporate custom user interfaces into Excel spreadsheets by way of UserForms and related Visual Basic for Applications programming code. 

Level: Advanced

Your Benefits of Attending:

  • Dressing up UserForms with Frames as well as using Frames to isolate sets of OptionButtons.
  • Testing the Plug and Print UserForm to see how additional worksheets appear automatically in the list of sheets that can be printed.
  • Creating a macro to launch a UserForm.
  • Triggering macros in Excel by way of CommandButtons added to UserForms.
  • Contrasting Option Buttons to CheckBoxes on UserForms for allowing a single selection from two or more choices.
  • Moving module sheets or UserForms from one workbook to another.
  • Incorporating a new feature that enables you to pick and choose the worksheets to print from any workbook—no programming knowledge or experience required.
  • Creating a UserForm or custom dialog box from scratch in Excel.
  • Adding CheckBoxes to UserForms to enable users to make selections.
  • Adding label controls to UserForms to add captions or display instructions.
Learning Objectives:
  • State the method in Visual Basic for Applications that would display a UserForm.
  • Recall the aspect of the Properties Window that allows you to change the wording that appears at the top of a Frame control.
  • Identify how to add CheckBoxes to UserForms to enable users to make selections.


On-Demand Webcast

Instructional Method:

NASBA Field of Study:
Computer Software & Applications (2 hours)

Program Prerequisites:
Parts 1 through 3 of this macros series, or equivalent prior experience with writing or editing programming code in Microsoft Excel.

Advance Preparation:

  1. Introduction

  2. Launching the Visual Basic Editor - 00:03:38

  3. Creating a UserForm - 00:06:00

  4. UserForm Overview - 00:07:28

    1. Labels - 00:09:00

    2. TextBoxes - 00:10:47

    3. ComboBoxes - 00:12:40

    4. ListBoxes - 00:15:37

    5. Option Buttons - 00:19:39

    6. Command Buttons - 00:21:01

    7. Frames - 00:22:20

  5. Create Plug and Print UserForm - 00:26:45

  6. Adding a Select Sheet(s) to Print Frame - 00:31:48

  7. Adding a ListBox to Store Worksheet Names - 00:33:20

  8. Adding a Preview Before Printing Frame - 00:35:44

  9. Adding Yes and no OptionButtons - 00:37:13

  10. Adding Command Buttons - 00:41:19

  11. Creating a UserForm_Initialize Event - 00:44:04

  12. Programming Code for

    1. UserForm_Initialize - 00:46:37

    2. OK Button - 00:54:51

    3. Cancel Button - 01:01:16

    4. Select All Button - 01:02:18

    5. Unselect All Button - 01:04:22

  13. Creating a Macro to Launch the UserForm - 01:08:14

  14. Accessing UserForm Launch Macro - 01:10:01

  15. Finishing UserForm Launch Macro - 01:11:56

  16. Create a Universal Print Macro Shortcut - 01:13:40

  17. Plug and Print UserForm - 01:22:07

  18. Additional Sheets Appear Automatically - 01:22:26

  19. Sample Data Entry UserForm - 01:24:22

  20. OK Button for Data Entry Form - 01:31:44

  21. Cancel Button for Data Entry Form - 01:37:51

  22. Moving Macros/UserForms to Other Files - 01:41:15

  23. Removing UserForms/Module Sheets - 01:43:58

Print Frame - 00:31:48
Project Explorer - 00:03:38
UserForm - 00:06:00, 00:44:04
ComboBoxes - 00:12:40
Command Buttons - 00:21:01, 00:41:19
Frames - 00:22:20, 00:35:44
Labels - 00:09:00
ListBoxes - 00:15:37, 00:33:20
Option Buttons - 00:19:39, 00:37:13
TextBoxes - 00:10:47

Combo Box: A form of control that is both a text field and a drop-down list. The user can either type information in the combo box directly or make a choice from the list.

Command Button: Command buttons can be added to worksheets or userforms to trigger macros.

ListBox: A ListBox is a control that you can add to a worksheet or user form to display a list of items. ListBoxes can be set to allow the user to make a single choice or multiple choices.

Macro: One or more lines of programming code that automate tasks. The Macro Recorder allows users to automate tasks without seeing the underlying programming code.

Quick Access Toolbar: A customizable shortcut toolbar that appears above the ribbon in Office 2007 and later.

TextBox : Textboxes are used in worksheets or userforms to display information or to allow the user to input information.

Guest Speaker

  • David H. Ringstrom, CPA

ATAAA Credit

Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in administrative.

ATATX Credit

Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in accounting.

ATAOP Credit

Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in operations.

CPE Credit

Continuing Professional Education

Aurora Training Advantage is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org.

For more information regarding administrative policies such as complaint and refund, and cancellation please contact our offices at 407-542-4317 or training@auroratrainingadvantage.com.

You must answer all questions during the webinar, view the recording completely and pass the test at the end with 70% correct answers to receive CPE credit.