Getting Started With VBA UserForms

04 Dec  Getting Started With VBA UserForms

Howdee! VBA userforms can be a huge help with a variety of different tasks, but can be somewhat tricky to get started with if you’ve never used them before and are new to programming. This article will serve as an introduction to VBA userforms and will cover the basics of designing them and subscribing to form events with your code. We will cover how to use a form to validate data entry into a sheet in Excel, as well as how to use a userform to pull user defined information into your code.

Creating a Basic VBA UserForm

Microsoft has made the creation of a VBA userform incredibly easy with the use of a built-in designer. You can create a userform using code, but I prefer to use the designer so I know my user interface is precisely what I want. To get started, press Alt + F11 to open the VBA editor, and then right-click on the project in the upper left you wish to add a userform to. From the menu that appears, select Insert, and then UserForm as shown below.

You should see a window like this pop up along with a toolbox.

The toolbox is where you’ll grab items you want on your userform and move them to the form canvas. If you hover over a tool icon, you will see a description of what each icon is. In this example, I’m going to create a basic data entry form to enter names and occupations. To do this, you’ll want to use the label and text box tools on your canvas. I’ve organized them like so:

I included the properties panel (located in the bottom left corner of the VBA editor window) here to show a few things. The (Name) property is important as that is how you will reference each element in your code later. So, since I named this VBA userform “DataEntryExample”, this is how I’ll reference it in my code when I need to. The other properties here will vary based on what you’ve selected in the designer, but all will look similar. It’s a good idea to browse these and become somewhat familiar with what properties are available for each element.

Now, for example’s sake, let’s say I have a sheet where the user will enter this data. When the user enters information and clicks submit, they’ll expect that information to be entered in to the sheet. When they click cancel, they’ll expect their changes to be discarded and the form to disappear.

To make this happen, we need some event handling. The easiest way to handle click events is to double click on the element you want to create an event handler for. So, when I double click the “Submit” button in my designer, the editor will automatically create a new event handler to handle the click event. For the submit and cancel buttons, I’ve entered the below code:

Private Sub Cancel_Click()
Me.Hide
End Sub

Private Sub Submit_Click()
Dim Name As Range, Occupation As Range

Set Name = Range("NameRange")

Set Occupation = Range("OccupationRange")

If Me.TextBoxName.Value <> "" Then
    If Me.TextBoxOccupation.Value <> "" Then
        Cells(Name.Rows.Count + 1, 1) = Me.TextBoxName
        Cells(Occupation.Rows.Count + 1, 2) = Me.TextBoxOccupation
    Else
        MsgBox "Occupation can not be blank. Please enter a value."
    End If
Else
    MsgBox "Name can not be blank. Please enter a value."
End If

End Sub

This is a pretty basic procedure that just validates that the values entered are not blank. Obviously you could then check if values were numeric, text, date, or within any set of parameters you needed. It’s also important to point out the use of “Me” in this code. The VBA userform that triggered these events can be referenced by using the keyword Me in your code. This makes it easy to make sure you’re always referencing the correct form/object.

Using a VBA UserForm Listbox

Many times, when we write macros, we are manipulating data on a spreadsheet. However, on the occasions when you need to display something to the user or have them interact with something where the spreadsheet isn’t an appropriate location to have this information displayed, userforms (and, more importantly a listbox) can be used to close this gap. For example, let’s say you need a user to select the correct file path for a particular routine. You could just use file dialog box (as explained in my post on looping through files here), but this can be limiting to someone who isn’t a power user. The user may not realize the code will or will not run for all subfolders in a directory or all files for that matter. Having a listbox of paths for a user to select is a bit more intuitive to non-power users in my experience.

To accomplish this, we can use a list box in a userform. Here is an example of one we will use to display a listbox of files in a folder:

Now, we’ll tie the following code to each button click event:

Private Sub ClearListBox_Click()
Me.FileListBox.Clear
End Sub

Private Sub Cancel_Click()

Me.Hide

End Sub

Private Sub LoadListBox_Click()
Dim MyFolder As String, MyFile As String

With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    .Show
    MyFolder = .SelectedItems(1)
    Err.Clear
End With

MyFile = Dir(MyFolder & "\", vbReadOnly)

Do While MyFile <> ""
    Me.FileListBox.AddItem (MyFile)
    MyFile = Dir
Loop

End Sub

A listbox is incredibly useful when a user needs to interact with information and keep it stored in memory for later use.  You can iterate over items in list boxes, allow users to select items from your list box, and even trigger events based on user double clicks of list box items.

I hope this brief introduction to VBA userforms was insightful to those of you not yet familiar with userforms in VBA. Stay tuned for a new article coming up on using VSTO to create richer, more functional user forms using Visual Basic or C#. I prefer this method now as the toolbox for VBA userforms is very limiting. However, for smaller scope projects, the VBA userform is more than adequate for your use.

As always, the file with all the code from this article is available in the example file section of the site for all my free members. Please let me know in the comments below or shoot me an email if you have any questions over VBA userforms. If there is interest I can dive deep into the use of some of the tools and events associated with userforms or listbox.

Cheers,

R