Course Outline – UserForms in Excel

This is a course outline where I will list all of the current categories and tutorials that will go into the UserForms in Excel free training course.

The goal of posting it here is to get your feedback on it’s setup, structure, and content.

This course is for you and I value your input! Please leave your thoughts and input in the comments section at the bottom of the page.

Course Outline

Introduction to UserForms

Get the user up-and-running with basic UserForms.

1. What is a UserForm?
   • Basic explanation of a UserForm for someone who doesn’t know what they are.
2. Simple Alternatives to UserForms
   • InputBox function and Msgbox function. There are specific tutorials for each function already on the site and they can be linked-to from here, but this tutorial should provide enough information for a user to get started using them in case they don’t need a full-fledged UserForm.
3. How to Create a UserForm
   • Basic step-by-step introduction to creating a UserForm: go through the basic steps of creating a UserForm and end up with a simple example. After reading this tutorial, the user should be able to create a very basic UserForm, open it, and close it.
4. Adding Controls to a UserForm
   • A few examples of adding controls to a UserForm; how to edit basic properties of the control; how to do basic formatting of the control. Have a section that lists all controls along with a link to the tutorial for each control (links can be added later once those tutorials have been created).
5. Showing a UserForm
   • Display/open/load the UserForm; briefly cover the UserForm initialization event and what that can be used for; change the display position of the UserForm; display a UserForm that allows you to also work in Excel while the form is still displayed (modeless) – also mention that this isn’t the best idea in most cases (there will be a tutorial specifically for modeless userforms in another section).
6. Closing a UserForm
   • Self-explanatory.

 

Using UserForms

Give the user more information on controlling and using a UserForm.

1. UserForm Properties
   • Overview of the properties window for the UserForm.
2. UserForm Control Properties
   • Overview of the properties section for UserForm controls.
3. Getting Data from a UserForm
   • How to take data from a control and put it into the worksheet or do anything else with it.
4. Putting Data into a UserForm
   • How to take data from a worksheet and an InputBox/Msgbox and put it into a UserForm.
5. Setting Default Values for Controls in UserForms
   • Setting default values when a form is initialized. This may be covered in control-specific tutorials, but it is a good idea to cover it here as a separate topic.
6. Clearing All Values in a UserForm
   • Multiple ways to clear a userform; cover them here in an easy-to-understand way.
7. Use Macros within UserForms
   • Basic explanation of how and where you use macros within a UserForm; How to call macros that are within in modules and worksheets, etc. (Remember that a user might not understand that macros can be used seamlessly with UserForms.)
8. UserForm Events
   • What they are and how to use them; how to get to the code window where you can control the events; then cover the events themselves that can be used for the UserForm. Have a separate section for each event and focus on the main events; have a small section for all other events that explain what they do/can be used for. (specific to the UserForm itself)
9. UserForm Controls Events
   • What they are and how to use them; how to get to the code window where you can control the events; then cover the events themselves that can be used for the Controls. Have a separate section for each event and focus on the main events; have a small section for all other events that explain what they do/can be used for. (specific to the controls for a UserForm)

 

UserForm Display and Appearance

Learn how to change the appearance of the UserForm and its Controls.

1. Changing the Appearance of a UserForm
   • Helpful examples and tips for making a UserForm look better/more professional.
2. Modeless UserForms
   • How to make them and when and why you might want to use them and when and why you wouldn’t want to use them. Hint: rare to use a modeless box.
3. Creating a Splash Screen in Excel
   • A window that pops-up when Excel opens that can say whatever you want it to say – makes the workbook look more professional. Make sure the title bar is removed and maybe don’t show any commands or controls on this screen; adding a logo to it as well makes it look a lot nicer.
4. Multi-Page UserForms
   • Example of having ‘tabs’ with different content.
5. Scrollable UserForms
   • Self-explanatory.
6. Resizable UserForms
   • Self-explanatory.
7. Removing the Title Bar from a UserForm
   • Self-explanatory.
8. Transparent UserForm
   • Self-explanatory.

 

Additional UserForm Topics

Everything that doesn’t fit anywhere else.

1. Loop Through the Controls in a UserForm
   • Loop through the controls and include how to get data from the controls or at least an example of why you might use the loop.
2. Selecting Ranges in the Worksheet from a UserForm
   • Basically, using the RefEdit control – good to have this in its own tutorial here because a user won’t know to look for “RefEdit” in order to use the feature.
3. Dependent ComboBox Drop Down Menus
   • What you select in one determines what will appear in the next one.
4. Multi-Column ComboBox Drop Down Menu
   • Data within the ComboBox is separated into multiple distinct columns.
5. Allowing Multiple Selections in a ListBox
   • Self-explanatory.
6. Getting Data from a ListBox Control
   • Include single and multiple selections.
7. Moving Items between ListBox Controls
   • Having two ListBox controls with an “Add” and “Remove” button between them that allows you to select items from one and add them to the other and also remove them from the other.
8. Disabling Buttons in UserForms
   • How to disable and enable command buttons within a UserForm; disabling the “Submit” or “OK” button until the form has been filled-out is a good example of when to use this.
9. Progress Indicator
   • Create a progress indicator for Excel, such as when an operation might take a long time – simple macro that iterates an action and pauses for a moment is a good way to present the indicator without actually doing much in the spreadsheet.

 

Working UserForm Examples

Fully working UserForm examples with Excel files included.

1. Data Input Template
   • Store the data on a visible or hidden worksheet.
2. Employee Information Template
   • Searches through a spreadsheet and returns the appropriate information in the UserForm.
3. Product Information Template
   • Searches through a spreadsheet and returns the appropriate information in the UserForm.

 

UserForm Controls

In this section, there will be a tutorial specific to each type of UserForm control.

In each tutorial, say how to add, edit, and use the control. Include how to reference them, get values from them, basically everything someone needs to do with them.

Format for these tutorial:

• How to add them to the UserForm.
• How to edit the control so that it will work and look good in the UserForm.
• How to add data to the control.
• How to get data from the control (if it contains any data).

As long as the examples are organized and at least the above elements are contained in each tutorial, additional examples and content for each control can be added, and is encouraged – the more examples the better.

1. CheckBox
2. ComboBox
3. CommandButton
4. Frame
5. Image
6. Label
7. ListBox
8. MultiPage
9. OptionButton
10. RefEdit
11. ScrollBar
12. SpinButton
13. TabStrip
14. TextBox
15. ToggleButton

 

Notes

If you want to make a tutorial for one of these topics, go to the homepage (TeachExcel.com) and click the link next to the tutorial that you want to make in the Requested Tutorials box.

If you upload a tutorial, please include a sample Excel file with it.

 

As always, please leave your thoughts in the comments section below!

Don