Instructor for this course
more

If you’re interested in learning how to incorporate user interfaces into Excel spreadsheets, you’ll want to take this course. In Macros Part 4, Excel expert David Ringstrom introduces you to the concept of UserForms. Step-by-step, David 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.  

 

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.

 

Course Series

This course is included in the following series:

4 CoursesExcel Efficiency: Macros

  1. Excel Efficiency: Intro to Macros Part 1
  2. Excel Efficiency: Intro to Macros Part 2
  3. Excel Efficiency: Intro to Macros Part 3
  4. Excel Efficiency: Intro to Macros Part 4

Learning Objectives

  • Identify the method in Visual Basic for Applications that would display a UserForm.
  • Discover how to move module sheets or UserForms from one workbook to another.
  • Recognize how to add CheckBoxes to UserForms to enable users to make selections.
Last updated/reviewed: February 11, 2019

19 Reviews (63 ratings)Reviews

4
Member's Profile
This course best illustrates how complex the macro coding process is and the fact that you're not going to learn it in a couple of 1 hr sessions - so temper expectations. There is a host of underlying code knowledge required to move from the simple macro recorder to highly functioning complex macros. I appreciate that the author acknowledged such and provided references for those that wish to pursue the next level.
5
Member's Profile
Thanks for opening the world of VBA. I have done some basic macro work - but when anything gets big turn to Visual FoxPro (my lifelong tool of choice). VFP has been deprecated - so this course has given me a nudge to do more within Excel when the opportunity presents itself.
5
Member's Profile
This is getting complicated, so if you didn't follow the previous lessons 100% this is going to be hard to follow. Probably best to review first. It was an instructive course and shows how to build some functionality and edit the macro to a greater degree.
3
Anonymous Author
Learned a lot but was probably the most difficult of the 4 part series. There was a lot more discussion versus seeing excel in action. I'm sure this was due to the complexity.
5
Anonymous Author
Very good examples of tools that can be used to make macros more efficient. Definitely one of the courses that one will need to view several times and/or practice with.
4
Anonymous Author
Good course. Worth having to expand your understanding of the range of applications macros can have especially to round out the previous 3 macro courses.
5
Anonymous Author
This course contains more vba functions and language of the 4 courses offered. Definitely suited for someone who wants to learn more about vba.
5
Anonymous Author
Great course that helps walk through things that can cause macro to result in errors when running based on user input. Very helpful.
5
Anonymous Author
Very informative for those that want to expand Excel abilities. Presenter moves through material quickly but in depth.
4
Member's Profile
Lots of information was covered. I will need to practice a lot to remember all that was covered.
5
Anonymous Author
Very helpful overview of how to use Visual Basic to customize/troubleshoot macros in Excel
5
Member's Profile
Great series! I intend to do all of David's courses available in Proformative/Illumeo
4
Anonymous Author
instructor does a great job demonstrating complex topics.
4
Member's Profile
Great intro and shown what is possible within Excel VBA
5
Member's Profile
Lots of good info on editing code behind macros.
5
Member's Profile
Comples issues explained well
4
Member's Profile
Clear instructions.
5
Anonymous Author
Great course
5
Anonymous Author
Very good.

Prerequisites

Course Complexity: Advanced

No Advanced Preparation or Prerequisites are needed for this course. However, it is recommended to take the other courses in the series prior to completing this one.

Education Provider Information

Company:
Illumeo, Inc., 75 East Santa Clara St., Suite 1215, San Jose, CA 95113
Contact:
For more information regarding this course, including complaint and cancellation policies, please contact our offices at (408) 400- 3993 or send an e-mail to .
Course Syllabus
INTRODUCTION AND OVERVIEW
  7:28Introduction to Excel Efficiency: Intro to Macros Part 4
  8:08UserForm: Overview, Labels, TextBoxes and ComboBoxes
  6:44UserForm: ListBoxes, Checkboxes, Option Buttons and Command Buttons
  9:25UserForm Frames and Create Plug and Print Userform
  9:31Adding a Select Sheet(s) to Print Frame, Adding a List Box to Store Worksheet Names, Adding a Preview ...
  5:17Adding Command Button and Creating a UserForm_Initialize Event
  8:17Programming Code for UserForm_Intialize
  7:26Programming Code for OK Button and for Cancel Button
  7:43Progamming Code for Select All Button and for Unselect All Button; Creating a Macro to Launch the UserForm
  11:59Accessing UserForm Launch Macro, Finishing UserForm Launch Macro and Create a Universal Print Macro Shortcut
  15:49Plug and Print UserForm, Additional Sheets Appear Automatically, Simple Data Entry UserForm and OK Button ...
  7:27Cancel Button for Data Entry Form, Moving Macros/UserForms to Other Files and Removing UserForms/Module Sheets
Continuous Play
  1:45:16Excel Efficiency: Intro to Macros Part 4 Full Video
SUPPORTING MATERIALS
  PDFSlides: Intro to Macros Part 4
  PDFIntro to Macros Part 4 Glossary/Index
  XLSXWorkbook: Macros Part 4
  XLSMPlug and Print
REVIEW & TEST
  quizREVIEW QUESTIONS
 examFINAL EXAM