Introduction to Excel Macros: Part 4 (Currently Unavailable)

Author: David H. Ringstrom

Part 4 of this series on Excel macros introduces the concept of UserForms in Excel. Excel expert David Ringstrom walks you through creating custom dialog boxes that can add interactivity in Excel and can better manage a user’s actions. David first walks you through the most common controls used within UserForms. He then builds a custom printing interface that he dubbed “Plug and Print,” which allow users to pick and choose the worksheets to print from any workbook. The presentation also shows how to create a basic UserForm for prompting users to input data, including making all fields required. Finally, you’ll see how to move module sheets and UserForms between workbooks, as well as how to remove them from workbooks when no longer needed.

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Office 365 version of Excel. David draws your attention to any differences in the older versions of Excel (2019, 2016, 2013, and earlier) 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 course.

Office 365 is a subscription-based product that provides new-feature updates as often as monthly. Conversely, the perpetual licensed versions of Excel have feature sets that don’t change. Perpetual licensed versions have year numbers, such as Excel 2019, Excel 2016, and so on.

Publication Date: November 2018

Designed For
Practitioners who wish to learn how to create and implement macros to automate their work in Excel.

Topics Covered

• 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 the property that allows you to specify or unspecify one or more items within a ListBox.

Level
Advanced

Instructional Method
Self-Study

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

Program Prerequisites
Experience with Excel

Advance Preparation
None