EXCEL VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

Ozgrid, Experts in Microsoft Excel Spreadsheets

 Excel Training VBA 2 Lesson 1

 

Introduction to Excel VBA Userforms


NEXT LESSON>> | BACK TO EXCEL VBA LEVEL 2 TRAINING INDEX

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

UserForms - Introduction

 

UserForms were first introduced into Excel in Excel 97. Prior to this it was Dialog sheets that were used. These are still available by right clicking on any Worksheet name tab and selecting Insert>MS Excel 5.0 Dialog. This will insert a Dialog sheet that has a Dialog box on it and by default the Forms toolbar will appear. However, they are only available for backward compatibility with previous versions of Excel and should only be used for this reason. The UserForms that are now available provide much greater flexibility for both the user and developer. The UserForm is still at times referred to as a Dialog box.

Perhaps the best result of the introduction of UserForms is not so much the Form itself but the ability for it to accept ActiveX Controls. These Controls provide far more flexibility than the Controls available on the Forms toolbar (OLE custom controls). They have far 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.

The UserForm, like most things in Excel, is an *Object and as so is a Member of the UserForms **Collection.

 

From Excels help....

*Object

A combination of code and data that can be treated as a unit, for example, a control, form, or application component. Each object is defined by a class.

**Collection

An object that contains a set of related objects. An object's position in the collection can change whenever a change occurs in the collection; therefore, the position of any specific object in the collection can vary.

.....End of Excels help.

 

For more detail see: UserForm Object, UserForms Collection in the VBE help.

 

Private Module

As the UserForm is a Object (similar to a Worksheet Object) it has it's own Private Module. Within this Private Module we can use anyone of the UserForms Events. These Events are listed below:


UserForm_Activate()
 
UserForm_AddControl(ByVal Control As MSForms.Control)
 
UserForm_BeforeDragOver(ByVal Cancel As MSForms.ReturnBoolean, ByVal Control As MSForms.Control, ByVal Data As MSForms.DataObject, ByVal X As Single, ByVal Y As Single, ByVal State As MSForms.fmDragState, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)
 
UserForm_BeforeDropOrPaste(ByVal Cancel As MSForms.ReturnBoolean, ByVal Control As MSForms.Control, ByVal Action As MSForms.fmAction, ByVal Data As MSForms.DataObject, ByVal X As Single, ByVal Y As Single, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)
 
UserForm_Click()
 
UserForm_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
 
UserForm_Deactivate()
 
UserForm_Initialize()
 
UserForm_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
 
UserForm_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
 
UserForm_Layout()
 
UserForm_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
 
UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
 
UserForm_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
 
UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
 
UserForm_RemoveControl(ByVal Control As MSForms.Control)
 
UserForm_Resize()
 
UserForm_Scroll(ByVal ActionX As MSForms.fmScrollAction, ByVal ActionY As MSForms.fmScrollAction, ByVal RequestDx As Single, ByVal RequestDy As Single, ByVal ActualDx As MSForms.ReturnSingle, ByVal ActualDy As MSForms.ReturnSingle)
 
UserForm_Terminate()
 
UserForm_Zoom(Percent As Integer)

As you can see many of the UserForm Events take arguments, for example in the: UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) we can use the Cancel argument to prevent a user from closing a UserForm via the X in the top right of the UserForm. To do this we would also need to use the CloseMode argument


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = 0 Then Cancel = True
End Sub


This simple bit of code would prevent the user from exiting the UserForm via the X, just ensure that you have a Cancel button on your UserForm! Then Cancel argument takes an Integer as it's argument. In case you are not familiar with True and False in the Visual Basic environment, False is equal to 0 (zero) and True is equal any other number. The other question that may spring to mind is just how did we no that a CloseMode of 0 means they used the X to close? Simply answer here is a message box.


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  
  MsgBox CloseMode
End Sub


Place this code into a UserForms Private module, run the Form and close via the X and we have our answer! In fact this is a handy way to find out many of Excel's UserForm return arguments.

Information Helpful? Why Not Donate | Free Excel Help
NEXT LESSON>> | BACK TO EXCEL VBA LEVEL1 TRAINING INDEX