Introduction to Excel VBA UserForms: Getting Started

Excel VBA UserForms are powerful tools that revolutionize the way we interact with data in Excel, offering a custom user interface experience that brings efficiency and precision to data entry and management. This blog post delves into the intricate world of Excel VBA UserForms, guiding you on how to insert userform in Excel and enhance Excel’s functionality. Whether you are a novice eager to explore the basics of UserForms or an experienced user looking for Excel VBA userform examples, this comprehensive guide offers valuable insights to you.

What is Excel VBA UserForm?

Excel Userforms are custom user interface screens that you can develop in VBA to interact with your users. With these types of forms, users can enter required data about themselves, making the process more convenient and controllable at the same time.

A userform has different components:

1. Userform’s object is a dialog box or window that creates a specific part of the user interface.
2. Userform’s collection is a collection whose elements represent each loaded form in an application. These are the collection elements:

• Count property: specifies the number of elements in the collection.
• Item method: (the default member) specifies a specific collection member.
• Add method: places a new userform element in the collection.

Excel VBA userforms have properties that determine appearance such as position, size, color, and behavior. They can also respond to events initiated by a user or triggered by the system. 

The role of Userforms in Excel’s functionality  

Userforms can enhance Excel’s functionality in several aspects:

Input and Filter Data Faster

With an Excel form, you don’t have to click into each cell to enter data; you can type it into a dialog box instead. Just fill out the text boxes, press “Enter,” and your data goes straight into the table, moving you to the next row automatically. Plus, finding certain data is easy too. There’s a “Criteria” button in the form that lets you search for records that match specific conditions.

Navigate within Your Tables Easier

If your Excel table is wide, scrolling side to side to see it all can be annoying. Excel VBA userforms make it easier by letting you enter or see a whole row of table data without the horizontal scroll. The form is set up vertically, making it easier to move through on your screen. Plus, it has a vertical scroll bar to make navigating even simpler. You can also use your keyboard’s arrow keys to quickly move through your data.

Avoid Input Errors with Data Validation

With Excel userforms, you can use data validation, which controls the type of data or values that are entered into a cell. For instance, you can make sure that users input only numbers into a date column of your spreadsheet. If they try to add text, they’ll receive an error message that prevents human errors significantly.

Create and Manage Surveys with Ease

If you add a userform to an Excel sheet, you can connect a form or survey directly to Excel. All survey results will appear in your spreadsheet as responses are submitted. After collecting survey results in Excel, analyzing your data becomes straightforward. You can craft graphs and pivot tables for deeper insights. Modifying your questions or employing data validation lets you set up various question formats, including multiple-choice, dropdown, and open-ended types, with ease.

how to Create a Userform?

You can insert userform in Excel using Visual Basic for Applications (VBA). Hence, it is necessary to enable the “Developer” tab. To do so, navigate to the File menu, and select Options.

In the Excel Options dialog box, select Customize Ribbon, and check the developer option as shown below.

Now the developer option is enabled on your Excel toolbar. Simply click on Visual Basics, and it will redirect you to the VBA window.

If you want to create a new userform, you have two different ways:

1.  Select Insert > Userform.

2. Click on the below icon and a new UserForm will be created:

Now, you have an Excel VBA userform within your sheet.

Excel VBA Userform Controls

Excel VBA UserForms are enriched with a variety of Controls, each designed to facilitate specific user interactions and data entry methods within Excel. Below is a detailed list of these Controls:

Button	Icon	Description
Select Objects		The only item that doesn’t create a Control. When you select it, you can only resize or move a Control that has already been drawn on a form.
Label		Creates unchangeable text, such as a table caption.
Text Box		Used to enter or change a text.
Combo Box		Creates a combo box, combining a list box and text box in one. Users can select an option from the list or type a value directly into the text box.
List Box		Displays a scrollable list of options, allowing users to select one or more items when the list exceeds the display area.
Check Box		Generates a checkbox, letting users easily mark an option as true or false, or select multiple options from a group.
Option Button		Presents a set of options, but users can select only one choice from the group.
Toggle Button		Creates a button that toggles on and off.
Frame		Enables creating a frame to visually or functionally group controls together. First, draw the frame, then place the controls inside it.
Command Button		Creates a command button, giving users a clear action point, such as submitting a form or executing a command.
Tab Strip		Creates multiple pages within the same section of a window or dialog box, enhancing organization and navigation in your application.
Multi-Page		Shows a compilation of different information screens as a unified collection.
Scroll Bar		Aids in swift navigation through extensive lists or volumes of data, marking the position on a continuum, or serving as a measure of speed or quantity.
Spin Button		Enables number adjustments up or down in conjunction with another control, or scrolling through a series of values or items.
Image		Shows a graphic, such as a bitmap, icon, or metafile, on your form. The Image control is for decorative purposes and consumes less memory than a Picture Box.

Designing Excel VBA Userforms   

To design the Userform’s layout and appearance, use the Properties window.

The most commonly changed properties are “Name” and “Caption”.

Property	Description	Example
Name	Assigns a name to the object for identification within the code.	Frame1
Caption	Sets the text contained in the caption	Employee Registration

Example of designing Excel VBA Userform:

Controls	Captions
Frames () → 2	Personal Data; Professional Data
Labels () → 6	Name; Age; Gender; Department; Desired Unit; Optional
Text Box () → 2	No caption
Option Button () → 3	Male; Female; Other
Combo Box () → 1	No caption (It is located next to the Label “Department” and “Age”)
Check Box () → 2	Transportation Vouchers; Health Insurance
Command Buttons () → 3	Register; Clear; Cancel

Tips for Arranging Controls Effectively

Consider these tips to arrange Controls when designing Excel VBA userforms:

• To align controls, select them, right-click, select align, and then the type you desire.

• To make controls the same size, select them, right-click, click on “Make Same Size”, and select the type you desire.

• To access Controls’ features, click on a certain Control, and navigate to the Format tab in the toolbar.

• When you’ve drawn part of a form, it’s often useful to group its controls together so that they behave as a single control.

Even after grouping, you can still format individual controls within the group separately. Ungrouping the grouped controls is also possible through the same path.

Common Control Properties in UserForms

Each control and form comes with a unique set of properties. Settings like Font and ForeColor, when applied to the form, become the default for its controls. However, you can change the property settings for each control in the properties window or with the VBA code. Here are the main Properties that you might want to change:

• Name: The Control’s name used in VBA programming

Try to choose a descriptive and easy-to-understand name as you may need to reference it when building the code of a userform.

• Caption: The text appearing on the Control

Depending on the item you use (command button, Frame, or Label), the caption can be placed differently.

• Font: Sets the size, format, and appearance of the font for the Control, except for its color.
• Fore Color: Sets the color of the font.
• Height, Width Properties: Sets the height or width, measured in points.

You can manually enter the height and width in the Properties Window, or they get updated automatically.

Syntax:  object.Height = Number;  object.Width = Number

To resize a Control with your mouse:

• Select the Control.
• Use the side handles to change the size horizontally or vertically as needed.
• To change both the horizontal & vertical size simultaneously, use the corner handle.
• Click and drag the handle to the required size, and then release the mouse button.
• Left, Top Properties

• Left Property sets the distance between the left edge of the Control and the left edge of the form.
• Top Property sets the distance between the top edge of the Control and the top edge of the form.

The Left and Top properties are measured in pixels. You can input these properties manually in the properties window.

Syntax:  object.Left = Number;  object.Top = Number

Value Property: Determines the selection status of a Control or specifies the Control’s content. Value property applies to all controls except Label, Frame, and Image and can be set in the Properties window or with VBA code.

Syntax:  object.Value = Variant

Here are more details about the value property:

• For Check Box, Option Button, and Toggle Button:

• An integer value of -1 means True and indicates that the Control is selected.
• A 0 value means False and indicates that the Control is cleared.
• The Null value indicates that the Control is neither selected nor cleared, and will appear shaded.
• For Scroll Bar and Spin Button:

It shows the current value, an integer that falls within the range defined by the Max and Min properties.

• For Combo Box and List Box:

It is the value in the Bound Column of the currently selected row (Bound Column property specifies the column from which the value is to be stored in a multi-column Combo Box or List Box when a row is selected by the user).

• For a Command Button:

It is a Boolean value which indicates whether the Control has been chosen.

• For a multi-page:

–          It is an integer value that indicates the current (active) page, and pages are numbered starting from zero.

• For a Text Box:

It refers to the text in the text box.

• Accelerator Property: Sets the shortcut key to access a Control.

This key is a single character, pressed in combination with it + Alt key. To click the ‘Enter’ command button in an Excel VBA userform, the accelerator key can be set as the letter “E” and used by pressing Alt+E. Accelerator property applies to the Controls Label, Check Box, Option Button, Toggle Button, Command Button, and Multi-Page.

• Note: The character used as the value of the accelerator is key-sensitive, meaning that setting the accelerator key as letter P is the same as letter p or the character + is the same as = since they are entered by pressing the same key.
• Alignment Property: Determines the position of a caption concerning the Control.

Alignment Property applies to the Controls Check Box, Option Button, and Toggle Button, and there are 2 settings for it:

1. fmAlignmentLeft (value 0) means the caption is positioned to the left side of the Control.
2. fmAlignmentRight (value 1) – caption appears to the right of the Control (the default setting)

• Note 1: Although the Toggle Button has Alignment as one of its properties, it is disabled and Alignment cannot be specified for the Toggle Button.
• Note 2: The text within the caption is always aligned to the left.
• Auto Size Property: A Boolean value (True/False) that specifies if the Control resizes automatically for its entire contents to be displayed.

TRUE value automatically resizes the Control while FALSE (Default option) keeps the size of the Control constant. Auto Size property applies to the Controls Label, Text Box, Combo Box, Check Box, Option Button, Toggle Button, Command Button, Image and Ref Edit.

• Back Color Property: Applies to all controls and sets the background color of Userform and its Controls.

It should be set to fmBackStyleOpaque for the Back Color to have any effect.

• Back Style Property: Sets the background style for an object and determines whether the background of controls will be opaque or transparent.

Back Style applies to the Controls Label, Text Box, Combo Box, Check Box, Option Button, Toggle Button, Command Button, Image and Ref Edit. It has two settings:

(I) fmBackStyleTransparent (value 0) for transparent background

(II) fmBackStyleOpaque (value 1) for opaque background (default)

It should be set to fmBackStyleOpaque for the Back Color to have any effect.

• Border Color Property: Sets the border color for Excel VBA Userform and its Controls.

Unless the Border Style sets a border, Border Color will have no effect. Border Style defines the border colors using the Border Color Property while Special Effect exclusively uses system colors to define its border colors.

Border Color applies to Userform and the Controls Label, Text Box, Combo Box, List Box, Frame, Image, and Ref Edit.

• Border Style Property: Specifies the type of border for an object.

It applies to the Userform and the Controls Label, Text Box, Combo Box, List Box, Frame, Image, and Ref Edit. It has two settings:

(I) fmBorderStyleNone (value 0) for no border;

(II) fmBorderStyleSingle (value 1) for a single-line border.

• Note: You cannot use both the Border Style and Special Effect properties simultaneously. Specifying a non-zero property for either one will automatically set the other property to zero, and if the Special Effect is set to zero, the Border Style is ignored.

 

 

Conclusion

In this blog post, we provided an introductory Excel userform tutorial. Excel VBA UserForm offers a versatile and powerful way to enhance user interaction and data management within Excel. By customizing UserForms and strategically using their components, you can significantly improve data entry efficiency, accuracy, and user experience. By exploring the potential of UserForms and incorporating them into your Excel toolkit, unlock new dimensions of productivity and efficiency in your data-driven tasks.

FAQs