How to Create User Forms using VBA in Excel

What is VBA, Why Should I Care?

Visual Basic for Applications (VBA) is the programming language behind Microsoft Office Products. It enables a programmer to automate these products. For example, you could write VBA code that enables the user to simply click a button. The program could scrape several websites containing stock information. If a certain stock meets a set condition, the program could dump that information into a nice pivot table within Excel. It could then send certain data to PowerPoint. The code could then take the graphs, tables, etc. that were automatically created in Excel and produce a slide deck. This slide deck could then be emailed to a set group of users within Outlook. All this can be completely automated. The applications of VBA are mind-blowing, in fact, I automated my job using VBA. Given the example above you can see how versatile VBA can be, especially if you have routine tasks, reports, and/or data that you run through any Microsoft Office product(s).

What Do I Need

You only need Microsoft Excel. A great feature about VBA is that you do not need a standalone editor, the editor is included within Excel. A fun place to start on your journey to VBA proficiency is creating user forms. I chose a UserForm as an introduction to VBA because it showcases how you can turn an otherwise boring Excel spreadsheet into a Graphical User Interface (GUI) with buttons, drop downs, and more.

What Will I Learn?

You will learn how to create a UserForm in Microsoft Excel. A UserForm is useful for countless applications. UserForms can be used to create grocery lists, address books, rosters, todo lists and a whole lot more! Creating a friendly GUI, allows even the most computer illiterate user to perform data entry tasks like an expert. Dramatically increasing efficiency; eliminating redundancy and restricting entries to only the available options. This is a recipe for success in any data entry task.

For this tutorial, I have created a UserForm that allows link builders an efficient way to track websites that they have outreached. A big part of Search Engine Optimization (SEO) involves building links with quality guest posts. Keeping track of all the websites requires a lot of organization, hence the UserForm. Of course, feel free to tweak it to your needs, that is half the fun and a great way to learn. This quick and easy UserForm records the website, contact information, type of link, previously contributed option, and any notes the user may have. The UserForm ensures uniformity and that required fields contain text. In addition, it allows a seamless data entry process and eliminates the need to navigate cells in the spreadsheet, saving time. The UserForm guides the user with the information that is wanted and limits the type of data entered to preset options. The UserForm also ensures the data is entered in the correct place and allows control over what format, style, and content are allowed, creating uniformity.

Let's Get Started!

Open up Microsoft Excel.

Enable the Developer tab to bring up the Visual Basic for Applications Editor. Go to File (top left of Excel).

Select "Options" at the bottom.

Select Customize Ribbon in the Excel Options window.

Under Main Tabs, check the Developer box. Click OK.

The Developer tab should now be visible in the Microsoft Excel ribbon.

To access the VBA editor, select the Developer tab and click the Visual Basic button (top left). Alternatively, you can access the editor by pressing ALT+F11 on your keyboard.

In the file tree window that displays your projects, select your workbook, most likely it will be labeled VBAProject (Book1). Expand the project and right click “ThisWorkbook”, select Insert, then select UserForm.

A UserForm and Toolbox will be displayed.

The Toolbox is a floating window that can be dragged wherever you would like. Select a control from the Toolbox and place it in the UserForm. Each control serves a different purpose. Feel free to try different controls. This tutorial covers what I consider the most popular controls.

Resize the UserForm as desired. In the Toolbox, select the Label Control (the capital A).

If you can’t see your Toolbox. You can bring it back up again by clicking the Toolbox icon at the top of the VBA editor.

Once you have the Label control selected, draw any labels that you want on your UserForm.

Now add a TextBox (icon that has “ab|”).

Draw the TextBox on your UserForm.

Now let’s add a Frame to our UserForm (the icon is a square with XYZ on top).

Draw a Frame where you would like. I like to use frames to isolate and label a control.

You can rename the Frame Caption in the Properties window on the left, as well as a myriad of other options; like fonts, images, colors, and more – play around!

Now let’s add some OptionButtons into the Frame.

You can change the Caption the same way that you did for the Frames.

You can also change the order of the TabIndex in the Properties section. The TabIndex starts at 0. When a user hits the Tab key, the focus will cycle through the TabIndex based on the order you specify. Very useful when you are entering data.

Now select a CheckBox and draw it on the UserForm. The Properties can be adjusted the same way as the other controls.

Draw another TextBox in the “Notes” Frame.

Then select the ScrollBar icon in the Toolbox.

Draw the ScrollBar on the right side of your new TextBox.

Next, we can add a CommandButton. Select the square icon and draw the CommandButton in the UserForm.

We will write code that determines what the CommandButton does, as well as the other Controls.

For a bit of fun, you can add an image to your UserForm. Click on an empty area in your UserForm and the Properties window will appear for the UserForm. In the Picture section, you can import an image to appear as the background.

By selecting a group of cells on your spreadsheet, you can Merge the cells to create a GUI illusion within your spreadsheet. I chose to do this and add blue as the background color. Additionally, I created a header: Website, Contact, Type, Previously Contributed?, and Notes. I also added a Border around those cells and made the text Bold.

Now for the code! Right-click the UserForm and select View Code, alternatively, you can hit the F7 button on your keyboard.

Copy and paste the code below. I have made notes within the code to help you understand what does what.

Option Explicit

Private Sub CloseButton_Click()
    Unload Me
End Sub

Private Sub OKButton_Click()
    
'Written by joshuabriansnow@gmail.com

Dim NextRow As Long

'   Make sure Sheet1 is active
    Sheets("Sheet1").Activate

'   Make sure a website is entered
    If WebsiteField.Text = "" Then
        MsgBox "You must enter a website."
        WebsiteField.SetFocus
        Exit Sub
    End If

'   Make sure an email is entered
    If EmailField.Text = "" Then
        MsgBox "You must enter a contact."
        EmailField.SetFocus
        Exit Sub
    End If

'   Determine the next empty row
    NextRow = Application.WorksheetFunction.CountA(Range("A:A")) + 4
        
'   Transfer the website
    Cells(NextRow, 1) = WebsiteField.Text
    
'   Create a border around cell
    Cells(NextRow, 1).Borders.LineStyle = xlContinuous
    
'   Transfer the email
    Cells(NextRow, 2) = EmailField.Text
    
'   Create a border around cell
    Cells(NextRow, 2).Borders.LineStyle = xlContinuous
    
'   Transfer the link type
    If OptionGuestPost Then Cells(NextRow, 3) = "Guest Post"
    If OptionResource Then Cells(NextRow, 3) = "Resource"
    If OptionOther Then Cells(NextRow, 3) = "Other"
    
'   Create a border around cell
    Cells(NextRow, 3).Borders.LineStyle = xlContinuous
    
'   Transfer previously published status
    If CheckBox1 = True Then Cells(NextRow, 4) = "Yes"
    If CheckBox1 = False Then Cells(NextRow, 4) = "No"
    
'   Create a border around cell
    Cells(NextRow, 4).Borders.LineStyle = xlContinuous
    
'   Transfer notes
    Cells(NextRow, 5) = NotesField.Text
    
'   Create a border around cell
    Cells(NextRow, 5).Borders.LineStyle = xlContinuous
    
'   Clear the controls for the next entry
    WebsiteField.Text = ""
    EmailField.Text = ""
    OptionGuestPost = True
    WebsiteField.SetFocus
    CheckBox1 = False
    NotesField.Text = ""
End Sub

You can place a button on your spreadsheet, that when clicked, will bring up your UserForm. Select the Developer tab in the Ribbon. Select Insert, then under the Form Controls, add a Button.

You can draw the Button on the spreadsheet, it will ask you to assign a Macro.

You are all done! Be sure to Save As a Macro-Enabled Workbook.

Wrapping up

We talked about how to get the editor set up within Excel. Touched base on several of the popular controls available in UserForms. We also went over some options in the Properties menu and learned how to bring it all together.

VBA is very versatile and certainly is not limited to UserForms. Remember when I gave the example of scraping websites for stock information? You could take that same example a bit further. Perhaps you could build a UserForm with CheckBoxes of several different stocks. You could write some VBA code to tell Excel to grab the current stock quote of only the stocks that have been checked, emailing certain people if the stock rises or drops at set points. The possibilities are endless, even on the go, you can run Excel through your smart phone.

You (hopefully) have made it to the end of this tutorial with a working example of a UserForm! Great job! It is my hope that this tutorial piques your interest in VBA. You can really impress in the workplace when you start automating! Perhaps you can even automate your job, or portions of it, freeing up time to write articles like this