First Project with a Form with Excel VBA

As the first project with a form, let’s create a UserForm that appears on the screen when a button on the worksheet is clicked.

Step 1. Create the Form

Using the Properties window, set the form’s properties as shown in Table.

Table. Form Properties

Step 2. Create the Button

On the worksheet, create a button and, using the Properties window, set its properties as shown in Table.

Table. Button Properties

Step 3. Add the Code

In the Sheet1 module, type the following code.

First Form – Worksheet Module

Private Sub cmdDemoForm_Click()
    frmFirst.Show
End Sub

Now, when you click the button, the form will appear on the screen.

How to Run the Project

A form can be linked to any control placed on the worksheet, as shown in the previous example.

In later examples (where the main objects are the form itself and its embedded controls), we will not repeat how the form is integrated with the worksheet — that task is left to the reader.

⚡ Tip: To test the code associated with the form, it is not actually necessary to place controls on the worksheet and link the form to them. After creating the form and writing the code in its module, simply:

• Select Run | Run Sub/UserForm, or
• Press F5, or
• Click Run Macro on the Standard toolbar.

The form will then be displayed on top of the active worksheet.

The Keyword Me

In code, the keyword Me is often used. It returns a reference to the currently active object (the form itself).

For example, instead of writing:

UserForm1.Caption = "Example"
Unload UserForm1

it is common to write:

Me.Caption = "Example"
Unload Me

Form with a Refreshable Background Image

You can set a picture as a form’s background using the Picture property. By default, the picture is displayed at its original size.

If you want the picture to stretch to fill the client area of the form, or scale proportionally to its width/height, you use the PictureSizeMode property. The PictureAlignment property defines the picture’s alignment within the client area (e.g., centered or aligned to the top-left).

Project: Background Image Switching

Let’s build a project where a form displays a background picture. When you click the form, the picture alternates between two images.

Step 1. Requirements

You need two bitmap images, e.g.:

• D:\1.jpg
• D:\2.jpg

Step 2. Form Properties

Create a form and set its properties as shown in Table.

Table. Form Properties

Step 3. Add the Code

Double-click the form and enter the following code in the form’s module.

Form with Refreshable Background Image

Private Sub UserForm_Click()
    Static flag As Boolean
    Dim filename As String
    If Not flag Then
        filename = "D:\1.jpg"
        Me.Picture = LoadPicture(filename)
        Me.PictureSizeMode = fmPictureSizeModeStretch
        Me.Caption = "Background Switching Pictures " & filename
    Else
        filename = "D:\2.jpg"
        Me.Picture = LoadPicture(filename)
        Me.PictureSizeMode = fmPictureSizeModeZoom
        Me.PictureAlignment = fmPictureAlignmentTopLeft
        Me.Caption = "Background Switching Pictures " & filename
    End If
    Me.Repaint
    flag = Not flag
End Sub

Explanation:

• The Static flag variable tracks which picture is currently displayed.
• The LoadPicture function loads an image file.
• PictureSizeMode = fmPictureSizeModeStretch stretches/shrinks the first image (possibly distorting proportions).
• PictureSizeMode = fmPictureSizeModeZoom scales the second image while preserving proportions.
• PictureAlignment = fmPictureAlignmentTopLeft aligns the image to the form’s top-left corner.

Note

It wasn’t strictly necessary to set the form’s Picture and PictureSizeMode properties in advance.

Alternatively, you could add the following initialization code:

Private Sub UserForm_Initialize()
    UserForm_Click
End Sub

This automatically triggers the Click procedure when the form loads, displaying the first image immediately.

Deleting a Picture

In the Properties window, a picture can be deleted by placing the cursor in the Picture field and pressing .

In code, this is achieved by assigning the Picture property to LoadPicture(«  »).
Example:

Me.Picture = LoadPicture("")

Form with a Tiled Background and Setting Properties at Initialization

An image can be displayed on a form not only as a single picture, but also as a tile.
In this case, the property PictureTiling must be set to True.
Naturally, you should also set the PictureAlignment property, which defines the placement of the initial image from which the tiled background is created.

Form property values can be set either in the Properties window or in code.
In the latter case, this is usually done in the Initialize event procedure, which is generated when the form is initialized but before it is displayed.

Example:
Build a form with a tiled background, with its properties set in code during initialization.

• Create the form.cel.

NOTE

• In the form’s module, enter the code.
• Ensure the required bitmap file is located in the default folder used by MS Ex

To check which folder is your default:

• Go to the File tab → Options.
• In the Excel Options dialog box, choose Save on the left.
• On the right, under Save workbooks, check the Default file location field.

The project checks for the existence of the image file in that folder using the Dir() function.

• If the file does not exist, the form will open without a tiled background.
• Dir() returns the name of a file or folder that matches the pattern passed to it (wildcards * and ? allowed).
• If no match is found, Dir() returns an empty string.
  Thus, checking whether a file exists is done by checking if Len(Dir(…)) = 0.

Closing a Form with

Clicking the Close button in the form’s upper-right corner closes it.

Question: Is it possible to close the form by pressing a key, such as ?
Answer: Yes.

You need to:

• Write code for the KeyDown event,
• Check for the required key code,
• Close the form using Unload or End.

The KeyDown event has two parameters:

• the key code,
• the modifier key identifier.

The constant for the key is vbKeyEscape.

Closing a form with

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

Confirming Form Closure

In many projects, it’s useful to request user confirmation before closing a form.

This can be done using the QueryClose event, which is triggered just before a form closes.

It has two parameters:

• Cancel → if set to –1, closure is canceled; if 0, the form closes.
• CloseMode → identifies the reason for closure.

Example:

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

Setting Form Position

The initial location of a form is set by the StartUpPosition property.

Table. StartUpPosition Values

Example:
Displays the form with its top-left corner at (100,100):

Private Sub UserForm_Initialize()
    Me.StartUpPosition = 0
    Me.Top = 100
    Me.Left = 100
End Sub

Modal and Modeless Forms

• A modal window is one that must be closed before the user can access another window.
• By default, UserForms in VBA are modal.

The Show method accepts an optional parameter style:

• vbModal (1) → modal
• vbModeless (0) → modeless

Example:

UserForm1.Show vbModeless   ' User can still interact with the worksheet
UserForm1.Show vbModal      ' Worksheet locked until form closes

Using Multiple Forms

A project may contain multiple forms.

• If one form replaces another in modal mode, the first must be closed before the second appears.
• In modeless mode, both forms can remain open, with the second slightly offset.

Modal – Worksheet Module

Private Sub cmdForm1_Click()
    UserForm1.Show vbModal
End Sub

Modal – UserForm1 Module

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

Modeless – Worksheet Module

Private Sub cmdForm1_Click()
    UserForm1.Show vbModeless
End Sub

odeless – UserForm1 Module

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

“Easter Egg”

An “easter egg” is a hidden dialog in an application — usually a programmer’s joke, often found in games.

Example:

• The easter egg appears only if the user right-clicks in the bottom-right one-ninth of the form’s client area.
• This means only someone who created the application would know how to reveal it.

The MouseDown event is used to identify the click point.

Event Syntax:

Private Sub object_MouseDown(ByVal Button As Long, _
                             ByVal Shift As Long, _
                             ByVal X As Long, _
                             ByVal Y As Long)

Parameters:

• Button – identifies the mouse button. Possible values (XlMouseButton):
  • xlNoButton
  • xlPrimaryButton
  • xlSecondaryButton
  • xlMiddleButton
• Shift – identifies pressed modifier keys (, , ):
  • 0 = none
  • 1 = Shift
  • 2 = Ctrl
  • 4 = Alt
    (Combinations return sums, e.g., Shift+Ctrl = 3).
• X, Y – coordinates of the mouse click relative to the form.