Étiquette : useform

The TextBox control is mainly used for user input, which is then used in the program, or to display the program’s calculation results.

The text entered in a TextBox can be converted into numbers or formulas in the code. The main event associated with the TextBox is the Change event.

Table 1: Basic TextBox Properties

Property	Description
Name	Sets the name of the text box
Text	Returns the text contained in the text box
Multiline	Boolean setting that defines whether the text box supports multiple lines
ScrollBars	Sets the display mode of scroll bars in the text box
SelLength, SelStart, SelText	These properties describe the selected text fragment within the TextBox
MaxLength	Sets the maximum number of characters allowed in the text box
PasswordChar	Defines the character displayed when entering a password

Adding Two Numbers

As an example using text boxes, let’s create a project where the sum of two numbers entered in two TextBoxes is calculated and the result is shown in a third TextBox, as shown in the figure below.

Create a UserForm with three Labels, three TextBoxes, and two CommandButtons. Use the Properties Window to set their values as shown.

Table 2: Property values defined in the Properties Window

Object	Property	Value
UserForm	Caption	c = a + b
Label	Caption	A
TextBox	Name	txtA
Label	Caption	B
TextBox	Name	txtB
Label	Caption	C
TextBox	Name	txtC
CommandButton	Name	cmdOK
	Caption	OK
CommandButton	Name	cmdANNULER
	Caption	CANCEL

In the form module, type the following code:

Private Sub cmdOK_Click()
    Dim a As Double, b As Double, c As Double
    a = txtA.Text
    b = txtB.Text
    c = a + b
    txtC.Text = c
End Sub

Private Sub cmdANNULER_Click()
    Unload Me
End Sub

Comments:

• The two numbers are entered into TextBoxes txtA and txtB, and the sum is calculated in txtC when the OK button is clicked.
• The syntax Unload Me closes the form when the CANCEL button is pressed.

Keyboard Shortcut Button

The Accelerator property of a control specifies a letter or number key that, when pressed along with the <Alt> key, triggers the control’s click event. This key must be part of the Caption string and appears underlined.
For example, to associate <Alt> + O with OK and <Alt> + A with CANCEL:

Private Sub UserForm_Initialize()
    cmdOK.Accelerator = "O"
    cmdANNULER.Accelerator = "A"
End Sub

<Enter> and <Esc> Keys

• The Default property set to True designates the button triggered by pressing <Enter>.
• The Cancel property set to True designates the button triggered by pressing <Esc>.

This means <Enter> finds the sum and <Esc> closes the form.

Private Sub UserForm_Initialize()
    cmdOK.Default = True
    cmdCancel.Cancel = True
End Sub

 

Locking the Result TextBox

• The Enabled property set to False disables the control completely (no focus).
• The Locked property set to True prevents editing while still displaying content.

Example:

Private Sub UserForm_Initialize()
    txtC.Enabled = False
End Sub

Prevent Button from Taking Focus

By default, clicking a button gives it focus. To keep focus on the current control, set TakeFocusOnClick to False:

Private Sub UserForm_Initialize()
    cmdOK.TakeFocusOnClick = False
End Sub

Move Focus with <Enter>

Use the KeyDown event to detect when the <Enter> key is pressed and move focus accordingly using SetFocus.

Private Sub txtA_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
    ByVal Shift As Integer)
    If KeyCode = vbKeyReturn Then
        txtB.SetFocus
    End If
End Sub

Private Sub txtB_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
    ByVal Shift As Integer)
    If KeyCode = vbKeyReturn Then
        cmdOK_Click
        txtA.SetFocus
    End If
End Sub

Private Sub UserForm_Initialize()
    txtC.Locked = True
End Sub

Comments:

• The KeyDown event occurs when a key is pressed.
• The KeyUp event occurs when the key is released.

Syntaxes:

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)

Parameters:

Element	Description
Object	Required. A valid object name
KeyCode	Required. Integer representing the key code
Shift	Required. Shift, Ctrl, Alt state during the event

Table 4: Shift Key Parameters

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

Common KeyCode Constants:

Constant	Value	Description
vbKeyReturn	0xD	Enter key
vbKeyEscape	0x1B	Escape key
vbKeyTab	0x9	Tab key
vbKeyBack	0x8	Backspace
vbKeyLeft	0x25	Left arrow
vbKeyUp	0x26	Up arrow
vbKeyRight	0x27	Right arrow
vbKeyDown	0x28	Down arrow
… (and many others)

To move focus, use Object.SetFocus.

ToolTip Text

Controls can have ToolTips using the ControlTipText property, which displays help text when the mouse hovers over the control.
Example:

Private Sub cmdOK_Click()
    Dim a As Double, b As Double, c As Double
    a = txtA.Text
    b = txtB.Text
    c = a + b
    txtC.Text = c
End Sub

Private Sub cmdANNULER_Click()
    Unload Me
End Sub

Private Sub UserForm_Initialize()
    txtA.ControlTipText = "Value of a"
    txtB.ControlTipText = "Value of b"
    txtC.ControlTipText = "Value of c"
    cmdOK.ControlTipText = "Sum of a + b"
    cmdANNULER.ControlTipText = "Cancel operation"
End Sub

The Label control is used to display information or captions. The user cannot modify the text displayed in the caption during program execution. The main property of the Label is the Caption property, which defines the text that is displayed.

A label does not display values from data sources or expressions; it is always unbound and does not change when you move from one record to another.

The following example shows different types of Labels: simple, with an image, and with a border. To implement this project, create a form in which you place three Labels. The image file D:\chiennoir.jpg is used. In the form module, type the following code:

Private Sub UserForm_Initialize()
   Me.Caption = "DemoLabel"
   Label1.Caption = "A simple caption"
   Label2.Caption = "A caption with image"
   Label2.Picture = LoadPicture("logo.jpg")
   Label2.PicturePosition = fmPicturePositionRightCenter
   Label3.Caption = "A caption with border"
   Label3.BorderStyle = fmBorderStyleSingle
   Label3.WordWrap = True
End Sub

Comments

â–  In the Property window of Label2, the image is loaded using the Picture property. The PicturePosition property determines the relative position of the image and the text.

â–  In the Property window of Label3, the BorderStyle property defines whether the text box is displayed with or without a border.

â–  The WordWrap property returns or sets a Boolean value that specifies whether the contents of a control automatically wrap at the end of a line, or whether the control expands to fit the text size.

An image in a form can be displayed not only as a whole image but also as a tiled background. In this case, the PictureTiling property must be set to True. You also need to take care of the PictureAlignment property, which sets the location of the initial image, from which the entire tiling pattern is constructed.

Form properties can be set using the Properties Window or in code. In the latter case, this is typically done within the Initialize event procedure of the form, which is triggered when the form is initialized but before it is displayed on the screen.

As an example, let’s build a form with a tiled background and set its properties in code during the form’s initialization phase.

Now, create a form and type the following code into the form’s module. Also, make sure the default folder that Excel uses contains the image file you want to display as a tiled background.

Private Sub UserForm_Initialize()
    Me.Caption = "Tiled"
    Me.BorderStyle = fmBorderStyleNone
    Dim imageA As String
    imageA = "image1.jpg"
    If Len(Dir(imageA)) > 0 Then
        Me.Picture = LoadPicture(imageA)
        Me.PictureAlignment = fmPictureAlignmentTopLeft
        Me.PictureTiling = True
    Else
        MsgBox "No file found at " & CurDir & "\" & imageA
    End If
End Sub

Remarks

• To find out which directory is currently set as default, go to the File tab on the ribbon and click the Options button. In the Excel Options window that opens, select the Save category on the left. On the right, in the Save Workbooks section, the Default local file location field will show the current working directory. You can change it if needed.
• This application checks for the existence of a bitmap file in the given directory using the Dir() function. If the file is not in the working directory, the form will launch without a tiled background.
• The Dir() function returns the name of a directory or file that matches the pattern passed as its argument.
• If no matching directory or file is found, Dir() returns an empty string. Therefore, checking for the existence of a file simply involves checking whether the length of the string returned by Dir() is zero. If it is zero, the file does not exist.

A background image can be incorporated into a form using the Picture property. This property displays the image at its original dimensions. If you want the image to fill the entire client area of the form or to stretch across its full width or height, use the PictureSizeMode property. The PictureAlignment property places the image within the form’s client area—for example, centered or aligned to specific sides of the form.

Let’s build a project with a form where an image is displayed as the background. When you click the form, two images will alternate.

To implement the project, you need two images. In this case:
C:\image1.jpg and C:\image2.jpg.

Now, create a form and use the Properties Window to set its property values as shown in the table below:

Table: Property Values Set in the Properties Window

Object	Property	Value
Form/UserForm	Picture	Link to bitmap file C:\image1.jpg
	PictureSizeMode	FmPictureSizeModeStretch
	Caption	Changeable Backgrounds

Then, double-click on the left mouse button inside the form, and in the opened UserForm module, type the following code:

Private Sub UserForm_Click()
    Static flag As Boolean
    Dim filename As String
    If Not flag Then
        filename = "C:\image1.jpg"
        Me.Picture = LoadPicture(filename)
        Me.PictureSizeMode = fmPictureSizeModeStretch
        Me.Caption = "Changeable Background Images " & filename
    Else
        filename = "C:\image2.jpg"
        Me.Picture = LoadPicture(filename)
        Me.PictureSizeMode = fmPictureSizeModeZoom
        Me.PictureAlignment = fmPictureAlignmentTopLeft
        Me.Caption = "Changeable Background Images " & filename
    End If
    Me.Repaint
    flag = Not flag
End Sub

Remarks

Now, when you click the form, the images C:\image1.jpg and C:\image2.jpg will alternate as background images.

• The image is loaded from a file using the LoadPicture function, whose parameter is the source filename.
• Since the value of the PictureSizeMode property for image C:\image1.jpg is fmPictureSizeModeStretch, it will stretch or shrink without keeping proportions to fill the form’s client area.
• The PictureSizeMode value for image C:\image2.jpg is fmPictureSizeModeZoom, so it will stretch or shrink proportionally to fit either the width or height of the client area.
• The PictureAlignment property set to fmPictureAlignmentTopLeft aligns the top-left corner of the image with the top-left corner of the form’s client area.
• In the Properties window, you can remove the image by placing the cursor in the Picture field and pressing the Delete key.
• In code, the image is removed by assigning an empty value to the Picture property, like this:

Me.Picture = LoadPicture("")

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