Custom Form or UserForm with a Tiled Background, Excel VBA

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.