The purchase allows the installation of this Excel Training Course to an unlimited number of PCs within your organization. Lesson 1 - UserForms - Introduction This lesson will introduce you to Excel VBA UserForms. UserForms were first introduced into Excel in Excel 97. It works like a Dialog box(sheet) and has superior Event handling that allows us to respond to a users actions in ways that were previously not possible. By this I mean we can have specific code run when the user clicks the control, enters, exits, double clicks, right clicks, types and much more. Basically we are able to capture any action that the user takes. If you want to give your projects a professional and consistent look and feel, then UserForms will certainly help you do this. Lesson 2 - Filling UserForm Controls The vast majority of UserForms that are designed within Excel are used so that users can easily select and input data. This also ensures that any entries that are entered into a spreadsheet are within the requirements needed. Excel has many Controls that can be placed on a UserForm that can make this not only easy for the user, but also for the designer of the UserForm. The two most useful Controls for this are the “ComboBox” and the “ListBox”. In this lesson, we will look at each of these Controls. Lesson 3 - MultiPage Control In this lesson we will look at the MultiPage control, arguably the most useful controls to use if your UserForm will be containing many different controls and/or you wish to have different controls associated with different aspects of your project. The other thing that we can do with a MultiPage is make our UserForm behave in the same manner as any one of Excel's standard Wizards. A Wizard by definition is an aid that steps you through a particular process. An example of this would be the Pivot Table Wizard or Chart Wizard. Lesson 4 - Validating The Users Inputs One of the biggest problems that is (or should be) faced by a developer (other than understanding just what they want) is developing a project that will only accept valid data. By far the quickest and easiest way to achieve this is to use a ListBox and/or a ComboBox that presents the user with only valid data. The approach is one that I will try to employ whenever possible. Unfortunately, it's not always possible to use a ComboBox or ListBox and we may need to allow the user to type in an entry. This means you will need to check whether the user has typed in a valid entry. In this lesson, we will explore how this can be achieved. Lesson 5 - When to Apply Validation What we shall look at in this lesson is when to reject or accept a users entries. By this I mean should we allow the user to completely fill out the UserForm first and then inform him/her that some of their entries are not valid, or would it be better to inform the user at each step. Lesson 6 - Which Controls to Use and When In this lesson, we will look at which controls should be use and for what purpose should we use them? Excel has basically a Control for every possible job type. It is important to know which Controls to use for which job. It is fair to say that there are no hard and fast rules for this as each project usually has something which makes it unique. So, we need to at times use a Control and modify it to suit our specific needs. However, there are certain guidelines that we can follow. Lesson 7 - Passing Control Values back to a Spreadsheet, Passing range values to the UserForm Controls In this lesson we will be looking at how we can pass values from the Worksheet to a Control or any number of Controls on a UserForm and also look at how we can do the opposite which is to pass the value from a Control back to the Worksheet. Lesson 8 - Option Buttons and Checkboxes in Detail In this lesson we will look at Option Buttons and Checkboxes in detail. The rules of which one to use and when are really quite simple and boils down to basically if the user should be allowed to make one selection only from a choice of many, then it should be OptionButtons. If the user should be allowed to make one or more choices from many, then it should be CheckBoxes. Lesson 9 - Creating Template Controls, Using the RefEdit Control Excel allows us to easily create Template Controls which we can format and name appropriately so that each time the Control is needed, rather than repeat the process over and over again, we can simply select the Template Control that we have created and place it on to a UserForm. This is extremely easy to do and can save many hours of repetitious work in the long run. We will look at Template Controls in this lesson. On top of that, we will look at the RefEdit Control. Normally used on a user form, the RefEdit control will display the address of a range, or single cell, that you've entered (typed in) or selected. Lesson 10 - Finding the ActiveControl, Creating Controls at Runtime At times when creating a project in Excel VBA which incorporates the use of a UserForm, you may wish to create actual Controls via the choice of a user. The advantage of actually creating the Control is the fact that it will require the need for less Controls on the UserForm at any one time, which in turn can lessen overheads. We will look at how this can be done in this lesson.
