Catégorie : Excel VBA Course

In projects, a situation often arises where user confirmation is required before closing a form. This behavior can be handled by using the QueryClose event procedure, which is triggered just before the window closes. This procedure has two parameters. If the first parameter is set to -1, the closing does not occur; if it is set to 0, the window closes. The second parameter identifies the reason that caused the window to close.

For example, in the following code, when you try to close the UserForm, a dialog box appears with two buttons: Yes and No, requiring user confirmation. If the user clicks Yes, the window closes; if No, it does not close.

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Select Case MsgBox("Close the window?", vbYesNo + vbQuestion)
        Case vbYes: Cancel = 0
        Case vbNo: Cancel = -1
    End Select
End Sub

Modal Window

A modal window is a window that cannot be closed without interacting with it first. By default, a custom window in VBA is modal. You can define the window type (modal or modeless) using the optional style parameter of the Show method.

Show Style

The style parameter has two valid values:

• vbModal or 1 for a modal form,
• vbModeless or 0 for a modeless form.

For example, with the following statement, the form window is displayed on the worksheet in modeless mode, and therefore, when the window is open, the user has access to the worksheet cells:

UserForm1.Show vbModeless

When the window is launched with the following statement, it is in modal mode, and therefore the worksheet cells are inaccessible to the user until the window is closed:

UserForm1.Show vbModal

Using Multiple Custom Forms

There can be multiple custom forms in a project. When switching from one form to another, it’s important to consider the mode (modal or modeless) in which the form is opened.

For example, add two forms—UserForm1 and UserForm2—to your project. Create a button on the worksheet and set its Name property to cmdForm1. When you press this button, the first form window will appear on screen.

The two macros below show how the code to invoke the second form when the first is clicked must differ depending on whether the window type is modal or modeless.

In Modal Mode

Before displaying the second form, you must close the first one in the code. In this case, only one form is ever shown on screen—either the first or the second.

Worksheet Module Code:

Private Sub cmdForm1_Click()
    UserForm1.Show vbModal
End Sub

UserForm1 Module Code:

Private Sub UserForm_Click()
    Unload UserForm1
    UserForm2.Show
End Sub

In Modeless Mode

It is not necessary to close the first form, and after clicking on the first form, both forms will be displayed on screen. To ensure both forms are visible at the same time, the second form is positioned slightly offset from the first.

Worksheet Module Code:

Private Sub cmdForm1_Click()
    UserForm1.Show vbModeless
End Sub

UserForm1 Module Code:

Private Sub UserForm_Click()
    UserForm2.StartUpPosition = 0
    UserForm2.Top = UserForm1.Top + 20
    UserForm2.Left = UserForm1.Left + 20
    UserForm2.Show
End Sub

To display a UserForm, you execute the Show method in a statement using the syntax FormName.Show. For example, if you followed the same steps as shown in the previous sections to create the UserForm frmEmployees, you could have a simple macro like this to call the UserForm:

Sub EmployeeForm()
    frmEmployees.Show
End Sub

If you want to see what the UserForm looks like when it is called in the actual worksheet environment, without having to write a formal macro yourself, you can type frmEmployees.Show in the Immediate Window and press Enter. The following figure shows how you and your users will see the example UserForm.

A UserForm is a VBA object class that has its own module. Similar to the concept that each worksheet has its own module, each UserForm you add to your workbook is automatically created with its own module. Accessing the module of a UserForm is easy: in the VBE, you can double-click the UserForm itself in the design pane; or in the Project Explorer, you can right-click on the UserForm name and select View Code, as shown in the following figure.

 

It is possible to close a custom form by pressing any key, such as . To do this, you simply need to write code that handles the KeyDown event, explicitly identify the required key, and close the form using the Unload or End statement. The KeyDown event has two parameters: the first returns the code of the pressed key, and the second identifies the modifier key pressed. VBA has a special constant vbKeyEscape for the Escape key code. The following code, placed in the custom form’s module, closes the window when the Escape key is pressed:

Private Sub UserForm_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
    ByVal Shift As Integer)
        If KeyCode = vbKeyEscape Then
            Unload Me
        End If
End Sub

 

Comments

• The KeyDown and KeyUp events occur sequentially when you press and then release a key.
  • The KeyDown event occurs when a key is pressed.
  • The KeyUp event occurs when a key is released.

Syntax:

Private Sub Object_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As fmShiftState)
Private Sub Object_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As fmShiftState)

These event syntaxes include the following elements:

Element	Description
Object	Required. The name of a valid object.
KeyCode	Required. Integer representing the code of the pressed or released key.
Shift	Required. State of the Shift, Ctrl, and Alt keys.

---

Table: Shift Parameter Constants

Constant	Value	Description
fmShiftMask	1	The Shift key was pressed
fmCtrlMask	2	The Ctrl key was pressed
fmAltMask	4	The Alt key was pressed

You can use the following KeyCode constants anywhere in your code instead of numeric values:

Constant	Value	Description
vbKeyLButton	0x1	Left mouse button
vbKeyRButton	0x2	Right mouse button
vbKeyCancel	0x3	Cancel key
vbKeyMButton	0x4	Middle mouse button
vbKeyBack	0x8	Backspace key
vbKeyTab	0x9	Tab key
vbKeyClear	0xC	Clear key
vbKeyReturn	0xD	Enter key
vbKeyShift	0x10	Shift key
vbKeyControl	0x11	Ctrl key
vbKeyMenu	0x12	Menu key
vbKeyPause	0x13	Pause key
vbKeyCapital	0x14	Caps Lock key
vbKeyEscape	0x1B	Escape key
vbKeySpace	0x20	Spacebar
vbKeyPageUp	0x21	Page Up key
vbKeyPageDown	0x22	Page Down key
vbKeyEnd	0x23	End key
vbKeyHome	0x24	Home key
vbKeyLeft	0x25	Left arrow key
vbKeyUp	0x26	Up arrow key
vbKeyRight	0x27	Right arrow key
vbKeyDown	0x28	Down arrow key
vbKeySelect	0x29	Select key
vbKeyPrint	0x2A	Print Screen key
vbKeyExecute	0x2B	Execute key
vbKeySnapshot	0x2C	Snapshot key
vbKeyInsert	0x2D	Insert key
vbKeyDelete	0x2E	Delete key
vbKeyHelp	0x2F	Help key
vbKeyNumlock	0x90	Num Lock key

UserForm Properties

The form has a wide range of properties that allow you to control both its appearance and operational settings. Of course, the most commonly used form properties are those that specify the name of the form and the text displayed in the title bar. The following table lists the main properties of the form.

Table 1: UserForm Properties

Property	Description
Name	Name of the UserForm
ActiveControl	Returns a reference to the control that has received the focus
BackColor	Background color
BorderColor	Border color
BorderStyle	Border style. Valid values are: fmBorderStyleNone and fmBorderStyleSingle
CanPaste	Defines whether an object can be pasted from the clipboard
CanRedo	Defines whether a redo operation is possible
CanUndo	Defines whether an undo operation is possible
Caption	Form caption (title)
Cycle	Specifies the behavior of elements in Frame and Page containers when focus is lost
DrawBuffer	Defines the memory size used when redrawing an image
Enabled	Determines whether the form is available to the user
ForeColor	Specifies the foreground color
Height, Width	Form height and width
HelpContextID	Link to a chapter in the help file
InsideHeight, InsideWidth	Height and width of the custom part of the form (excluding title bar and border thickness)
KeepScrollBarsVisible	Shows scrollbars. Valid values are: fmScrollBarsNone, fmScrollBarsHorizontal, fmScrollBarsVertical, fmScrollBarsBoth
Left, Top	Coordinates of the top-left corner of the form
MouseIcon	Assigns a custom mouse pointer
MousePointer	Specifies the type of mouse pointer
Picture	Specifies a link to a bitmap file used as a background
PictureAlignment	Specifies the alignment of a bitmap used as a background
PictureSizeMode	Determines whether the image should be scaled
ScrollHeight, ScrollWidth	Sets the height and width of the scrollable area
ScrollLeft, ScrollTop	Sets the top-left coordinate of the scrollable area
SpecialEffect	Sets the appearance of the form
StartUpPosition	Specifies the starting position of the form
Tag	Specifies a value used to identify a specific form
VerticalScrollbarSide	Determines which side of the form the scrollbars appear on
Visible	Sets the visibility of the form
WhatsThisButton	Specifies whether to show a Help button (?)
Zoom	Specifies the zoom level of the displayable element

UserForm Methods

A form has many methods that allow you to perform a wide range of operations.

Table 2: UserForm Methods

Method	Description
Copy	Copies the contents of the object to the clipboard
Cut	Copies and removes the object content to the clipboard
Hide	Hides the form without removing it from memory
Load	Loads an object into memory without displaying it
Move	Moves the form
Paste	Pastes the clipboard contents
PrintForm	Prints an image of the form
RedoAction	Repeats the last redo command
Repaint	Refreshes the image of the form
Scroll	Scrolls the image
SetDefaultTabOrder	Sets the default tab order for tab key navigation
Show	Displays the form
UndoAction	Repeats the last undo command
Unload	Removes an object from memory
WhatsThisMode	Displays a Help pointer with a question mark

UserForm Events

Events allow you to create programs that manage the entire lifecycle of a form, from initialization to closing.

Table 3: UserForm Events

Event	Description
Activate, Deactivate	Occurs when the form is activated and deactivated
AddControl	Occurs when a control is added
BeforeDragOver	Occurs during data drag
BeforeDropOrPaste	Occurs before dragged data is inserted
Click	Occurs when the user clicks the form
DblClick	Occurs when the user double-clicks the form
Error	Occurs when the form encounters an error but cannot send a message
Initialize	Occurs when the form is initialized
Layout	Occurs when the layout of the form changes
KeyDown, KeyUp	Occurs when the user presses or releases a key while the form has focus
KeyPress	Occurs when the user presses a key other than function, arrow, or service keys, while the form has focus
MouseDown, MouseUp	Occurs when the user presses or releases a mouse button
MouseMove	Occurs when the user moves the mouse pointer over the form
QueryClose	Occurs before the form window is closed
RemoveControl	Occurs when a control is removed
Resize	Occurs when the form is resized
Scroll	Occurs during scrolling
Terminate	Occurs when the form is closed
Zoom	Occurs when the zoom level of the form changes

The first step in creating a UserForm is to insert one into the Visual Basic Editor. To do this, press Alt + F11 to access the VBE and select your workbook’s name in the Project Explorer, as shown in the following figure.

Accessing the VBE Environment

With the workbook name selected, click on Insert / UserForm from the menu bar, as illustrated in the next figure.

Inserting a UserForm

A new UserForm opens in its design window, as shown in the following figure.

A New UserForm Opens