VBA (Visual Basic for Applications) is a powerful tool that lets Excel users automate tasks and create custom solutions for their data needs. Among the many features VBA offers, UserForms stand out as an essential tool for creating interactive forms. They enhance user experience, provide data validation, and allow for dynamic interactivity that goes beyond basic Excel spreadsheets.

In this blog, we’ll dive into the world of VBA UserForms in Excel, covering everything from creating your first form to advanced functionality. By the end of this guide, you’ll have the knowledge to design professional and interactive UserForms in Excel. Here’s our follow-along practice sheet: VBA userform.xlsm

What are VBA UserForms in Excel?

A VBA UserForm is a customizable dialog box in Excel where users can input, edit, or view data. It provides a graphical interface (GUI) for users, making it easier to interact with data instead of working directly in cells.

Key features:

• Input data through text boxes, combo boxes, or list boxes.
• Validate data before saving to ensure accuracy.
• Display messages or data dynamically.

Why Use UserForms in Excel?

Here’s why UserForms are invaluable:

• Enhanced User Experience: Replace repetitive tasks with intuitive interfaces.
• Data Validation: Ensure inputs meet specified criteria.
• Efficiency: Automate tasks like adding or editing records.
• Professionalism: Provide end-users with a polished interface.

Setting Up Your Environment

Before creating a UserForm, ensure your environment is ready:

1. Enable the Developer Tab:
   • Go to File > Options > Customize Ribbon.
   • Check the Developer option.

2. Access the VBA Editor:
   • Press Alt + F11 or go to Developer > Visual Basic.
3. Insert a UserForm:
   • In the VBA Editor, right-click on any workbook in the Project Explorer.
   • Select Insert > UserForm. This will create a new userform and a toolbox will automatically appear for you to work with.

Let’s now understand how a userform’s components work by taking up a scenario and building a VBA UserForm from scratch for the same.

Imagine you are designing an Employee Management System for an HR team to manage employee records. The system requires a UserForm to add, edit, or delete employee details and review their department and designation. 

The HR UserForm includes:

1. Common Controls: Labels, TextBoxes, ComboBoxes, ListBoxes, CommandButtons, CheckBoxes, and OptionButtons.
2. Populating Data: Automatically fetches department names, designations, and existing employee data.
3. Adding Functionality to Buttons: Includes buttons for submitting, editing, and deleting employee records.
4. Data Validation and Error Handling: Ensures required fields are filled correctly before saving.
5. Advanced Features: Dynamically updates the form controls based on user actions.

We will take a look at each of these individually but before that, let’s see what can be done with the default userform screen that we see. Navigate to the left pane on your VBA editor after clicking the userform to see its Properties. Here, one can update the form’s caption, background color, and also replace it with a picture, etc.

https://1drv.ms/v/c/95fe973e3f396cf0/EWpgBWifKXlMrvoLslPVAMABe6-sEl30WhZmWV4xY84jCA

**

Common Controls in UserForms

Controls in VBA UserForms are interactive elements that allow users to interact with the form. They enable data input, selection, and action triggering within a UserForm. Each control has unique properties, methods, and events that define how it behaves and interacts with the user.

1) Label

Purpose: Labels are used to display static text or descriptions, such as field names or instructions, in a UserForm.

• Properties:
  • Caption: The text displayed by the label.
  • Font: Customize the font type, size, and style.
  • BackColor and ForeColor: Set the background and text color.
  • WordWrap: Allows text to wrap within the label if it’s too long.
• Example Use: Displaying field names like “Name,” “Age,” or “Department” next to corresponding input controls.

Let’s see how we can create labels for our Employee Management System:

Label – Made with Clipchamp_1733844891688.mp4

**

2) TextBox

Purpose: TextBoxes allow users to input text, numbers, or other data into the UserForm.

• Properties:
  • Text: The value entered by the user.
  • MaxLength: Limits the number of characters the user can enter.
  • MultiLine: Allows multiple lines of text (e.g., for comments).
  • PasswordChar: Masks the input (e.g., for passwords).
• Events:
  • Change: Triggered whenever the text in the TextBox changes.
  • Exit: Triggered when the TextBox loses focus.
• Example Use: Collecting user input, such as names, ages, or addresses.

For some of the labelled fields that we created earlier, we are going to create input fields using the textbox option in the toolbox:

textbox -corrected – Made with Clipchamp_1733847781728.mp4

**

3) ComboBox

Purpose: ComboBoxes display a dropdown list of options, allowing users to select one. They also support manual text input if enabled.

• Properties:
  • AddItem: Adds items to the dropdown list.
  • List: An array of items in the ComboBox.
  • Value: The selected item.
• Events:
  • Change: Triggered when the selection changes.
  • DropButtonClick: Triggered when the dropdown button is clicked.
• Example Use: Selecting predefined options like departments, regions, or product categories.

Now, we shall use the ComboBox to create dropdown list for our “Department” and “Designation” fields:

dropdown – corrected – Made with Clipchamp_1733853520526.mp4

**

4) CheckBox

Purpose: CheckBoxes allow users to make one or more selections from a set of independent options.

• Properties:
  • Value: Returns True if checked, False if unchecked.
  • Caption: The text displayed next to the box.
• Events:
  • Click: Triggered when the box is checked or unchecked.
• Example Use: Letting users opt into newsletters or choose preferences.

Let’s insert a checkbox in our EMS form to ask the user if they are a contractor or full-time employee:

checkbox – corrected – Made with Clipchamp.mp4

**

5) OptionButton

Purpose: OptionButtons (also called Radio Buttons) allow users to select one option from a group. They are often used in scenarios where only one choice is valid.

• Properties:
  • Value: Returns True if the button is selected.
  • Caption: The text displayed next to the button.
• Events:
  • Click: Triggered when the button is selected.
• Grouping: Place OptionButtons within a Frame to ensure selections are mutually exclusive within that group.
• Example Use: Selecting gender, payment methods, or yes/no options.

Let’s include a new option / radio button to allow the user to specify their gender:

option button – Made with Clipchamp_1733853723593.mp4

**

6) CommandButton

Purpose: CommandButtons are clickable buttons used to trigger actions, such as saving data, clearing fields, or closing the form.

• Properties:
  • Caption: The text displayed on the button.
  • Enabled: Determines whether the button is active.
  • BackColor: Sets the background color.
• Events:
  • Click: Triggered when the button is clicked.
• Example Use: “Submit,” “Cancel,” or “Clear” buttons.

Now let’s include a submit button which will insert a record in the main database once clicked. In the below video, you will see how the commandButton is added and what the underlying code would be to facilitate its inserting functionality. You can also modify the code to do other actions like deleting, modifying records, etc. 

Final – Made with Clipchamp.mp4

The explanation of the VBA code will be covered in a subsequent section.

**

How to Use ChatGPT to create VBA UserForms?

Creating VBA UserForms can be challenging, especially if you’re new to programming. ChatGPT can act as your virtual assistant, providing guidance, generating code snippets, and helping troubleshoot issues. Let’s see if we can leverage ChatGPT effectively for building VBA UserForms components like ListBoxes and modify our CommandButton Action code as needed. 

Before we get into the ChatGPT zone, let’s first understand what a listbox is:

ListBox

Purpose: ListBoxes display a list of items, allowing users to select one or multiple options depending on its configuration.

• Properties:
  • MultiSelect: Defines if the ListBox allows multiple selections.
  • List: An array of items in the ListBox.
  • Selected: Returns True if an item is selected.
• Events:
  • Click: Triggered when an item is selected.
  • DblClick: Triggered when an item is double-clicked.
• Example Use: Displaying a list of employees, products, or tasks for selection.

Now, that this is established, take a look at the below video to understand how you can a create multi-select skills list that users can select from. The values selected will be added to a new column called “Technical Skills” in our main database table. You will also see how we can modify the Submit Button’s VBA code using ChatGPT to incorporate this new listbox component.

Prompt:

“I have a multiselect listbox also added now to my user form. All the values selected in it must go as an entry in one of the columns called ‘Technical Skills’ in sheet2. How should I modify the below code to do this task?

<Insert previous version of CommandButton_Click() VBA module code>

“

Listbox – chatgpt – Made with Clipchamp_1733901299780.mp4

As you can see, we successfully used chatGPT to incorporate this new component in our interactive UserForm. You can also use this AI tool for getting the base code or debugging issues for you.

**

Explanation of VBA Module Code to Insert Data

Purpose: Define and set the worksheet (Sheet2) where the data from the user form will be written.

ThisWorkbook: Refers to the workbook containing the code.

Sheets(“Sheet2”): Specifies the target sheet where the data is written.

**

Purpose: Determines the next empty row in Sheet2 in column B (where the data begins).

ws.Rows.Count: Gets the total number of rows in the worksheet (e.g., 1,048,576 in most Excel versions).

ws.Cells(ws.Rows.Count, “B”).End(xlUp): Starts from the bottom of column B and moves up to find the last non-empty cell.

Row + 1: Calculates the next empty row.

**

Purpose: Writes the values entered in the form controls to specific columns in the next empty row.

Mapping Form Controls to Columns:

• TextBox1: Employee Name (Column B)
• ComboBox1: Designation (Column C)
• TextBox2: Date of Birth (Column D)
• ComboBox2: Department (Column E)
• TextBox5: Email ID (Column F)
• TextBox6: Contact Number (Column G)
• CheckBox1: Contractor (Column H; “Yes” if checked, “No” otherwise)
• OptionButton1 and OptionButton2: Gender (Column I; “Male” or “Female”)

**

Purpose: Declare variables for the ListBox processing:

• i: Counter for iterating through the items in the ListBox.
• selectedSkills: String to store all selected skills concatenated together.

**

Purpose: Checks each item in the ListBox to see if it is selected and adds it to selectedSkills.

ListCount: The total number of items in the ListBox.

Selected(i): Checks if the item at index i is selected.

List(i): Retrieves the text of the item at index i.

selectedSkills & Me.ListBox1.List(i) & “, “: Appends the selected item to selectedSkills, followed by a comma and space.

**

Purpose: Removes the trailing comma and space if there are selected items.

**

Purpose: Writes the concatenated string of skills into column J (10th column).

**

Purpose: Resets all form controls to their default state after submission.

**

Purpose: Displays a confirmation message to the user.

**

Overview – Create a VBA UserForms in Excel

• Open the VBA Editor: Press Alt + F11 to open the Visual Basic for Applications editor.
• Insert a UserForm:
  • Click on Insert > UserForm from the menu.
  • A blank UserForm will appear along with the Toolbox.
• Add Controls to the UserForm:
  • Use the Toolbox to drag and drop controls like Labels, TextBoxes, ComboBoxes, ListBoxes, CheckBoxes, OptionButtons, and CommandButtons onto the UserForm.
  • Adjust the layout by resizing and positioning the controls as needed.
• Customize Control Properties:
  • Open the Properties Window to modify control attributes such as:
    • Name: Unique identifier for each control (e.g., txtName, btnSubmit).
    • Caption: The text displayed on controls like Labels and Buttons.
    • Font/Color: Set styles for better readability.
• Write VBA Code for Events:
  • Open the Code Window by right-clicking the UserForm or control and selecting View Code.
  • Write event-driven code (e.g., what happens when a button is clicked or a dropdown value is selected).
• Link the UserForm to Excel:
  • Create an Excel macro to open the UserForm using UserFormName.Show.
  • Assign this macro to a worksheet button or shortcut.
• Test the UserForm:
  • Run the UserForm by executing the macro or interacting with the assigned button.
  • Verify that all controls and event handlers work correctly.
• Save the Workbook:
  • Save the workbook as a macro-enabled file (.xlsm) to retain the UserForm and VBA code.
• Refine and Debug:
  • Fix any issues and enhance the design or functionality based on user feedback.

Best Practices for UserForms

1. Keep it Simple: Avoid cluttering your form with too many controls.
2. Validate Inputs: Always validate user inputs to ensure data accuracy.
3. Use Error Handling: Prevent runtime errors from crashing the form.
4. Test Thoroughly: Test your UserForm for all possible scenarios.

FAQs About UserForms

Q1. Can I use UserForms without VBA?

No, UserForms require VBA for creation and functionality.

Q2. How do I share a UserForm?

Save your workbook as a macro-enabled file (.xlsm) and share it.

Q3. Can I use UserForms across multiple workbooks?

Yes, but you’ll need to export and import the UserForm module.

Conclusion

VBA UserForms in Excel are a game-changer for Excel users, offering a robust way to create interactive and user-friendly interfaces for managing and processing data. They provide flexibility to design tailored solutions, from simple data entry forms to complex applications involving dynamic controls, error handling, and advanced automation.

With features like ComboBoxes, TextBoxes, CommandButtons, and more, UserForms enable efficient data collection, validation, and manipulation. By incorporating advanced techniques like populating controls dynamically and integrating with external data sources, you can create scalable, professional-grade systems for business or personal use.

The true power of UserForms lies in their ability to simplify complex workflows and empower users to interact with data seamlessly. While the learning curve may seem steep initially, consistent practice and exploration of their potential can transform the way you work in Excel, making you more efficient and unlocking endless possibilities for innovation. Create your first UserForm today by using our practice sheet as a template: VBA userform.xlsm