[Excel VBA] How to Set UserForm Title and Size via Code

2025年12月26日

Introduction

When creating UserForms in VBA, you typically set the text on the title bar (Caption) and the size of the form (Width and Height) using the Properties window. However, there are times when you want to change these values dynamically. For example, you might want to “change the form title based on the open file name” or “adjust the form size depending on the amount of data to be processed.”

By using VBA code, you can easily set or change these properties just before displaying the UserForm. In this article, I will explain the basic method for dynamically setting the UserForm’s title and size.

Sample VBA Code to Set Title and Size

This macro sets the title, width, and height for a UserForm named DataEntryForm and then displays it. Usually, macros that display forms are written in a standard module.

Complete Code for Standard Module

' Set UserForm properties and display it
Sub ShowCustomizedForm()

    ' Specify the target form using a With block
    With DataEntryForm
    
        ' Set the title using the .Caption property
        .Caption = "Data Entry Form Ver. 1.2"
        
        ' Set the form width in points using the .Width property
        .Width = 300
        
        ' Set the form height in points using the .Height property
        .Height = 250
        
        ' Apply settings and show the form
        .Show
        
    End With

End Sub

How to Use

In the Visual Basic Editor (VBE), ensure that the (Name) property of your UserForm is set to DataEntryForm.
Paste the ShowCustomizedForm macro above into a standard module.
When you run this macro, the UserForm will appear with the specified title and size.

Code Explanation

`With DataEntryForm ... End With`

By using the With statement, you specify the target object (DataEntryForm) once. After that, you can simply access properties starting with a dot (like .Caption), which makes the code cleaner and easier to read.

`.Caption = "Data Entry Form Ver. 1.2"`

The .Caption property controls the text displayed in the form’s title bar. If you use a variable here, such as .Caption = ActiveWorkbook.Name & " Data Entry", you can display a dynamic title that adapts to the situation.

`.Width = 300` and `.Height = 250`

.Width: Sets the width of the form.
.Height: Sets the height of the form.

The unit used here is “points.” 1 point is approximately 0.35mm. This is the same unit used in the Properties window, so it is easy to find the optimal size in the Properties window first and then apply that number to your code.

Summary

In this article, I explained how to dynamically set the title and size of a UserForm using VBA code.

Change the title with the .Caption property.
Change the width with the .Width property.
Change the height with the .Height property.

By simply setting these properties before calling FormName.Show, you can provide a more flexible and professional-looking UserForm. Please try using this in your own tool development.

よかったらシェアしてね！