User Form Interface Layout in Microsoft Products using Visual Basic for Applications

Introduction (written by an AI)

Today I want to share my experience with VBA (Visual Basic for Applications) in Microsoft Office products such as Excel, Word, Access, and PowerPoint. If you are already familiar with VBA and want to expand your skills, this article is for you! We will focus on creating user form interfaces using code rather than the graphical editor, which allows for dynamic interface adaptation and the automation of routine tasks.

In this article, we will cover how to set up user forms and controls such as text boxes, buttons, and dropdown lists using VBA code. By programming in VBA, you can change the appearance and functionality of forms “on the fly, ” making your interface more flexible and suitable for various tasks.

I also want to draw your attention to the fact that VBA is accessible to users with disabilities, such as blind users. Working with VBA allows them to create and customize user forms without relying on a graphical editor, which may be incompatible with screen readers.

I hope this article will be useful to you, help improve your skills in creating user interfaces in Microsoft Office applications using VBA, and enhance your everyday toolkit. I wish you pleasant reading and success in exploring the world of VBA!

A brief aside

This article is intended for those who are already somewhat familiar with VBA, understand what a procedure is, and how to change object properties. I’m writing it because such an article would have made my life much easier two months ago.

Adding a control to a form

To add a control to a form using code, you need to:

1. Initialize an object variable with the appropriate type

For all controls, you can use the Control type, but it doesn’t support event handling (you won’t be able to respond to interactions with it). It would look like this:

Dim MyElement As Control

However, as recommended by the official Microsoft documentation, I suggest using the specific type for each control (a table with types is provided below), for example:

Dim MyCommandButton As CommandButton

To enable event handling for the control, add the WithEvents parameter to the declaration like this:

Dim WithEvents MyCommandButton As CommandButton

2. Use the Set operator and the Controls.Add method to add the control to the form and assign a reference to it to the variable.

Programmatic (dynamic) creation of user form controls in VBA is done using the Controls.Add method.

Syntax

Set [Var] = [Form].Controls.Add([ProgID], [Name], [Visible])

Explanation of method components

• Var — the object variable from the first step, to which the created control will be assigned.
• Form — the name of the user form on which the control is being added.
• ProgID — the programmatic identifier of the control being created (listed in the table below).
• Name — optional parameter, specifying the name of the added control.
• Visible — optional parameter, determining the visibility of the control. Can be either True or False.

ProgID and Name in Controls.Add expressions are string parameters and should be enclosed in quotation marks if they are not variables. See examples below.

Control element identifiers

Here’s a table referenced earlier.

Examples

To implement the examples, open an Excel workbook with macro support (.xls or .xlsm) and, in the VBA editor, create a user form UserForm1 and a module Module1.

Macro

Let’s start with a short procedure that will call the form initialization procedure. This is necessary because the code written in the form object cannot be run as a macro.

Module1:

' Declare a procedure named "Start"
Sub Start()
' Call the "Init" procedure of the "UserForm1" object
Call UserForm1.Init
End Sub

Example 1, TextBox

Programmatically creating a TextBox control and assigning its Text property the value “Hello World!”. Since Text is the default property of TextBox, instead of MyTextBox.Text, you can simply use the variable name MyTextBox.

To edit the code of the user form, right-click on it to open the context menu and select “View Code”.

UserForm1:

' Initialize the object variable
Dim WithEvents MyTextBox As msforms.TextBox

Sub Init()
' For aesthetics, we’ll name our form
UserForm1.Caption = "My UserForm"
' Add the TextBox to the form
Set MyTextBox = UserForm1.Controls.Add("Forms.TextBox.1", "myTextBox1")
' Set the text
MyTextBox = "Hello World!"
' Show the form
UserForm1.Show
End Sub

Since we named the newly created TextBox “myTextBox1”, we can now refer to it by this name, for example:

UserForm1.Controls("myTextBox1") = "Hello World!"

This line can replace:

myCont = "Hello World!"

As you can see, it’s more convenient to refer to the added control through a variable rather than by its assigned name.

While you could place this code in a module directly, I recommend keeping the interface-related code within the form to avoid confusion.

Now, if you run the Start procedure in Module1, you’ll see a window titled “My UserForm” with a text box at the top.

To perform actions when the “MyTextBox” field changes, you need to create a procedure in the form with the object variable’s name and the event name (for text boxes, the default event is Change), separated by an underscore. For example:

UserForm1:

Sub MyTextBox_Change()
' Show a message box with the new text content
MSGBox MyTextBox.Text
End Sub

Example 2, ComboBox

Creating a ComboBox control in Excel using VBA, assigning values, setting dimensions, and margins.

UserForm1:

Dim WithEvents MyComboBox As msforms.ComboBox

Sub Init()
Set MyComboBox = UserForm1.Controls.Add("Forms.ComboBox.1")
' Use the With block to avoid repeating the element’s name multiple times
With MyComboBox
' Assign the List property an array with the values we want in the list
.List = Array("Green", "Blue", "Red", "Black", "White")
' Set the dimensions and left margin
.Width = 200
.Height = 20
.Left = 20
End With
' Beautify the form a little
With UserForm1
.Caption = "Love color"
.Height = 60
.Width = 250
.Show
End With
End Sub

Example 3, OptionButtons and Frames

The feature of OptionButton controls is that, unlike checkboxes, only one option button can be selected at a time. To create multiple groups of option buttons, they need to be placed inside frames. For this, like with other controls, create a frame and add the option buttons inside it.

UserForm1:

Dim WithEvents MyFrame As Frame
Dim WithEvents MyOptionButton1 As msforms.OptionButton
Dim WithEvents MyOptionButton2 As msforms.OptionButton
Dim WithEvents MyOptionButton3 As msforms.OptionButton

Sub Init()
' Add the frame
Set MyFrame = UserForm1.Controls.Add("Forms.Frame.1")
' Add option buttons
Set MyOptionButton1 = MyFrame.Controls.Add("Forms.OptionButton.1")
With MyOptionButton1
' Set caption and top margin so they don’t overlap
.Top = 0
.caption = "OptionButton1"
End With
Set MyOptionButton2 = MyFrame.Controls.Add("Forms.OptionButton.1")
With MyOptionButton2
' Set caption and top margin so they don’t overlap
.Top = 20
.caption = "OptionButton2"
End With
Set MyOptionButton3 = MyFrame.Controls.Add("Forms.OptionButton.1")
With MyOptionButton3
' Set caption and top margin so they don’t overlap
.Top = 40
.caption = "OptionButton3"
End With
UserForm1.caption = "OptionButtons list"
UserForm1.Show


End Sub

Conclusion

Similarly, you can add other controls and modify their properties. All the properties of each control can be found in the official Microsoft documentation. I hope this article was helpful to you!