<<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>>
Ozgrid, Experts in Microsoft Excel Spreadsheets

 Excel Training VBA 2 Lesson 4


UserForms Order of Events Procedures

Information Helpful? Why Not Donate | Free Excel Help >> Excel Training-Video Series >> Build Trading Models In Excel

Workbook Download
    This is a zipped Excel Workbook to go with this lesson.

UserForms Order of Events

The first Event that will fire when you either "Show" or "Load" your UserForm is the "Initialize" Event.  This will occur immediately after the UserForm is loaded, but before it is shown (visible). It is important to understand that, if the UserForm was already Loaded (but not visible) the Initialize Event would not fire by using the Show Method. This is because the UserForm would already be loaded into memory. This means that the Show Method will Load a UserForm, if it's not already, but will only make it visible if already loaded.

The next Event that will fire is the "Activate" event.  It is important that you know the difference between "Initialize" and "Activate".  While "Activate" will occur if you "hide" then re-show a UserForm, the "Initialize" Event will not.  This is because the UserForm has not been unloaded from memory. So this means that the Initialize Event will only fire when the UserForm is loaded into memory, while the Activate Event will fire whenever the UserForm is made visible. The order of any Events after this is dependant on the action taken by the user.


The term Focus is described by Excel as:

The ability to receive mouse clicks or keyboard input at any one time. In the Microsoft Windows environment, only one window, form, or control can have this ability at a time. The object that "has the focus" is normally indicated by a highlighted caption or title bar. The focus can be set by the user or by the application.

In Excel 2000 UserForms now have a new Property called "ShowModal".  This is a Boolean Property as it can either be set to True or False with the default being True.  If the Property is left set as "True", the user will not be able to shift Focus to any other Objects except the Controls of the UserForm itself.  This means that while the UserForm is visible, the user will not be able to select cells on a Worksheet.  If the Property is set to "False", the User will be able to select cells and operate menu bar options on a Worksheet.  When designing UserForms for other users, I would strongly recommend leaving the default as "True" otherwise the user will be able to make changes that you have no control over.

As a simple exercise to get you acquainted with UserForms I would like you to insert a UserForm as a shell and code it so that it is Loaded into memory and visible by clicking a CommandButton that is attached to a Worksheet I would also like you to give your UserForm a meaningful name and set up a "Cancel" button so that your form is unloaded from memory when this button is clicked.

I will keep this first lesson simple to allow you to ask any questions relating to what we have discussed.

Please find attached a Workbook with four buttons on a Worksheet that will hopefully help you understand the Events and use of Keywords we have discussed.  After using each button take a look at the code for each.

Information Helpful? Why Not Donate | Free Excel Help