Excel VBA–No Experience Necessary: Lesson 5

In this month’s article we will walk through, step by step, the project described at the end of last month’s Lesson 4. There’s a fairly significant number of tasks to accomplish to implement this, so it deserves a thorough dissection. (Maybe that’s not the proper term. It’s not quite dead yet.)

The project involves creating an Excel phone list with a user form serving as an interface. The form is to include one TextBox for name and another for telephone number, and Previous, Next, Add, Delete, and Close buttons. I encourage you to open up Excel and follow along with each step.

Step 1 – Create the Form

Starting with a new workbook (Excel file), bring up the VBA Editor (Alt+F11). Select Insert, User Form from the menu. If the Properties window does not appear automatically, bring it up with View, Properties Window (or F4). Change the Name property of the form to PhoneListForm, and the Caption property to My Phone List.

Step 2 – Add Controls

Still in the VBA Editor, if the Controls Toolbox is not already open, bring it up with View, Toolbox. Each control type’s name appears in a hint balloon as you pass your mouse over its button. Add the following controls to your form, and change the properties as specified:

• Label control – set Caption property to "Name:"
• TextBox control – set Name property to "txtName"
• Label control – set Caption property to "Telephone:"
• TextBox control – set Name property to "txtPhone"
• CommandButton control – set Name to "cmdPrev" and Caption to "Previous"
• CommandButton control – set Name to "cmdNext" and Caption to "Next"
• CommandButton control – set Name to "cmdAdd" and Caption to "Add"
• CommandButton control – set Name to "cmdDelete" and Caption to "Delete"
• CommandButton control – set Name to "cmdClose" and Caption to "Close"

Resize and move your controls so that your form looks something like this:

Step 3 – Implement UserForm_Activate Subroutine

I mentioned a couple of hints at the end of the last article. The first was to create a subroutine called UserForm_Activate to set the initial contents of the form. To do this, right-click on the form and select View Code. This will bring up the code window for your form and create a subroutine called UserForm_Click. We’re not really going to do anything special when the user clicks on the form, so go ahead and change the subroutine to UserForm_Activate. This subroutine is executed when the form is first brought up.

We will set the initial contents of the two TextBox controls using the Text property. The values will come from the first two cells in the first row.

Private Sub UserForm_Activate()
    ' Read initial values from Row 1:
    txtName.Text = Cells(1, 1).Value
    txtPhone.Text = Cells(1, 2).Value
End Sub

In this step, let’s also implement the Close button, since it’s just a single line of code. Switch back to the UserForm window and double-click on the Close button to add its subroutine, cmdClose_Click. (You can also do this directly from the code window by selecting cmdClose from the dropdown list of the form’s controls which appears at the top-left of the code window.) Use “Unload Me” to close the form:

Private Sub cmdClose_Click()
    Unload Me ' Close the form
End Sub

Step 4 – Add a Button to the Spreadsheet

This is more or less copied straight out of last month’s article. We’re going to add a button to the spreadsheet itself which will, when clicked, bring up our form. Switch back to Excel and click on View, Toolbars, Control Toolbox. Click on the button for the CommandButton control, then place it on your spreadsheet by clicking and dragging. To view the buttons properties, click the button in the upper right of the Toolbox. Change the name to “cmdShowForm”, and the caption to “Show Form”.

Next, double-click on the button. This will switch you back to the VB Editor and automatically create your cmdShowForm_Click subroutine. Add the line of code to show your form:

Private Sub cmdShowForm_Click()
    PhoneListForm.Show
End Sub

Next, let’s try out the form. Switch back to Excel and click the Design Mode button in the Control Toolbox (upper left). Put a name and telephone number in cells A1 and B1, then click the Show Form button. Your form should now appear, with the name and number from Row 1 appearing in the text boxes.

Note: Make sure you always close your form when you’re ready to switch back to the VBA Editor. You will be unable to change any code while your form is still active.

Step 5 – Add a Row Number Variable

Since we will be allowing the user to move up and down the list, we’ll need a way to keep track of which row number the form is currently displaying. We will do this by creating a "form level" variable. This is a variable declared within the code of the form, but not within any of its subroutines. At the top of the code window for your form, add the line:

Dim lCurrentRow As Long

A form level variable can be used in any of the form’s subroutines. Since our form starts by displaying the contents of row number 1, we should initialize this variable to 1 in the UserForm_Activate subroutine. To be really good programmers, we’ll then use this variable in the next two lines to read the name and telephone number. That way, if we decide for some reason to start in a row other than the first, we’ll only need to change one line of code:

Private Sub UserForm_Activate()
    ' Read initial values from Row 1:
    lCurrentRow = 1
    txtName.Text = Cells(lCurrentRow, 1).Value
    txtPhone.Text = Cells(lCurrentRow, 2).Value
End Sub

Step 6 – Implement Previous, Next Buttons

In the VBA Editor, bring up the user form and double-click the Next button to create its subroutine cmdNext_Click. In this subroutine we will move from the current row to the next row by adding 1 to our lCurrentRow variable, and reading in the name and telephone number from the new row. To add 1 to a VB variable, use the algebra-defying statement: var = var + 1:

Private Sub cmdNext_Click()
    ' Increment row number:
    lCurrentRow = lCurrentRow + 1

    ' Show contents of new row in the form:
    txtName.Text = Cells(lCurrentRow, 1).Value
    txtPhone.Text = Cells(lCurrentRow, 2).Value
End Sub

As you may have guessed, code for the Previous button will be remarkably similar. However, we have to be a bit more careful. Excel will not be pleased with us if the user is already in row 1, clicks the Previous button, and we try to read values from row 0. We will need to use an if statement to make sure the row number is greater than 1 before we attempt to subtract from it:

Private Sub cmdPrev_Click()
    ' Show previous only if not already in first row:
    If lCurrentRow > 1 Then
        ' Decrement row number:
        lCurrentRow = lCurrentRow - 1

        ' Show contents of new row in the form:
        txtName.Text = Cells(lCurrentRow, 1).Value
        txtPhone.Text = Cells(lCurrentRow, 2).Value
    End If
End Sub

Now switch back to Excel and try out your form. You should be able to move up and down the list. Of course, if you haven’t typed names and numbers into the spreadsheet, there won’t be much to see.

Which brings me to the next point. Something’s missing! We are supposed to be able to use our form to add new names and numbers. Of course, we haven’t implemented the Add button yet, but suppose we are skipping merrily through our list with the Next button, and make a change to one of the existing entries. If we click Next again, that change should be saved back to the spreadsheet before showing the next row, right? (Right.)

Well, that should be easy. In our Previous and Next subroutines, we will simply read the contents of the form and save it back to the spreadsheet before we change rows. We already have this line of code to read the name from the spreadsheet and display it in the form:

    txtName.Text = Cells(lCurrentRow, 1).Value

To go the other way, i.e. to read the name from the form and save it in the spreadsheet, we simply turn the line of code around:

    Cells(lCurrentRow, 1).Value = txtName.Text

Our revised subroutines, then, are:

Private Sub cmdPrev_Click()
    ' Show previous only if not already in first row:
    If lCurrentRow > 1 Then
        ' Save form contents before changing rows:
        Cells(lCurrentRow, 1).Value = txtName.Text
        Cells(lCurrentRow, 2).Value = txtPhone.Text
        
        ' Decrement row number:
        lCurrentRow = lCurrentRow – 1

        ' Show contents of new row in the form:
        txtName.Text = Cells(lCurrentRow, 1).Value
        txtPhone.Text = Cells(lCurrentRow, 2).Value
    End If
End Sub

Private Sub cmdNext_Click()
    ' Save form contents before changing rows:
    Cells(lCurrentRow, 1).Value = txtName.Text
    Cells(lCurrentRow, 2).Value = txtPhone.Text
    
    ' Increment row number:
    lCurrentRow = lCurrentRow + 1

    ' Show contents of new row in the form:
    txtName.Text = Cells(lCurrentRow, 1).Value
    txtPhone.Text = Cells(lCurrentRow, 2).Value
End Sub

Try your form again. You’ll see that we’re in the homestretch.

Step 7 – Implement Add Button

Since the Next and Previous buttons now do the actual saving of data back to the spreadsheet, the Add button has a fairly simple job. Since each row in the spreadsheet represents a different person, to add a new person to the list, we simply have to find an empty row to put them in. Of course, we can tell how many rows are currently in use with UsedRange.Rows.Count. So our Add button will simply set lCurrentRow accordingly and clear the text boxes on the form. After the user types in the new name and number, clicking Previous or Next will save.

Private Sub cmdAdd_Click()
    ' Set current row to first empty row, i.e. one row after
    '  the last row currently in use:
    If Cells(1, 1).Value = "" Then  
       lCurrentRow = 1  ' (list is empty - start in row 1)
    Else
       lCurrentRow = ActiveSheet.UsedRange.Rows.Count + 1
    End If
    ' Clear the form for user to add new name:
    txtName.Text = ""
    txtPhone.Text = ""
    ' Set focus to Name textbox:
    txtName.SetFocus
End Sub

Try it out. Bring up your form, type in a name and number, click Next or Previous. It works, doesn’t it? Well, yes and no...

If you hadn’t noticed, we have a slight problem here. Suppose you are trying to add multiple names to the spreadsheet. You should be able to click Add, type in a name/number, then click Add again to type in the next name/number, without having to click Next to save the first name/number. This is basically the same issue we had with our Previous and Next buttons. By clicking Add, we’re changing the current row, which means we should first store any changes the user made to the original row. This will allow us to add multiple new names consecutively, and will also take care of the case where the user makes a change to one of the names, then clicks Add to add a new name.

To save the contents of the form before going to the first blank row, we add the same code we used in cmdPrevious_Click and cmdNext_Click:

Private Sub cmdAdd_Click()
    ' Save form contents before changing rows:
    Cells(lCurrentRow, 1).Value = txtName.Text
    Cells(lCurrentRow, 2).Value = txtPhone.Text
       …

While we’re at it, let’s add the same code to our Close button, so any changes the user was making before closing the form are not lost:

Private Sub cmdClose_Click()
    ' Save form contents before closing:
    Cells(lCurrentRow, 1).Value = txtName.Text
    Cells(lCurrentRow, 2).Value = txtPhone.Text
    Unload Me ' Close the form
End Sub

Step 7 – Implement Delete Button

Last button! If the user clicks the delete button, we simply need to delete the current row (name/number) from the spreadsheet. But since we’re conscientious programmers, we’ll put in one of those annoying “Are you sure?” message boxes first.

The MsgBox function, which I mentioned briefly at the end of Lesson 2 as a simple means to display information, also allows you to set an optional second parameter controlling what types of buttons are displayed at the bottom of the message (OK/Cancel, Yes/No, Yes/No/Cancel, etc) as well as what, if any, type of icon you’d like to display on the message box (question mark, “i” for information, red X, etc). For example, the line of code:

    MsgBox "Do you really want to delete it?", vbQuestion + vbYesNo

will show a message box with a question mark icon and Yes and No buttons. vbQuestion and vbYesNo are predefined VBA constants.

In order to tell whether the user clicked Yes or No, we have to check the return value of the MsgBox function. A message box with Yes and No buttons will return either vbYes or vbNo (again, VBA constants).

    If MsgBox("Do you really want to delete it?", _
                  vbQuestion + vbYesNo) = vbYes Then
        ...
    End If

In our code, we’ll use the name of the person currently displayed in the form as part of the message box. We will also specify the caption of the message box, “Confirm Delete” using an optional third parameter of the MsgBox function. After deleting the row, we will display the contents of the new current row.

Private Sub cmdDelete_Click()
    Dim smessage As String
    smessage = "Are you sure you want to delete " + txtName.text + "?"
    If MsgBox(smessage, vbQuestion + vbYesNo, _
              "Confirm Delete") = vbYes Then
        ' Delete current row:
        Rows(lCurrentRow).Delete
        ' Show contents of new current row in the form:
        txtName.text = Cells(lCurrentRow, 1).Value
        txtPhone.text = Cells(lCurrentRow, 2).Value
    End If
End Sub

Step 8 – Tighten Up That Code

You’ve probably noticed by now that there are a couple of basic things our code is doing in several different places. We have six different subroutines for our form, and five of them include code to set the text in the form’s text boxes. Four of the subroutines save the contents of the form back to the spreadsheet. Since we have only two text boxes, this is not much of an issue. But just suppose you decided to really make something out of this little project, and began to add more text boxes for address, city, state, zip, fax, e-mail address, website and pet names. Clearly you don’t want to have to put code for each of these text boxes in nine different places. What you need is to take your redundant code and break it out into separate subroutines, such as LoadRow and SaveRow:

Private Sub LoadRow()
    txtName.text = Cells(lCurrentRow, 1).Value
    txtPhone.text = Cells(lCurrentRow, 2).Value
End Sub

Private Sub SaveRow()
    Cells(lCurrentRow, 1).Value = txtName.text
    Cells(lCurrentRow, 2).Value = txtPhone.text
End Sub

Go back and change your other subroutines to call these new subroutines. For example:

Private Sub cmdPrev_Click()
    ' Show previous only if not already in first row:
    If lCurrentRow > 1 Then
        ' Save form contents before changing rows:
        SaveRow
        ' Decrement row number:
        lCurrentRow = lCurrentRow - 1
        ' Show contents of row in the form:
        LoadRow
    End If
End Sub

Now, for each new textbox you add, you will simply need to add one line of code each to the LoadRow and SaveRow subroutines.

Do More

I’ve included my spreadsheet with the form and all of the code which appears above: Lesson 5 Example.xls.

There are other things we could do to this project as part of the goal to “tighten things up”. For example:

• add code to the SaveRow subroutine which checks each TextBox to make sure it is not empty before adding it to the spreadsheet. If one is empty, display an error message.
• write code in cmdNext_Click which prevents the user from going more than one row past the last entry in the list.
• write code in cmdDelete_Click which ignores the delete button if currently on an empty row

There are even more things you could do to make this project more useful, besides adding additional fields for address, e-mail, etc:

• Add a “List” button which brings up a separate form listing all of the names in the spreadsheet. Allow the user to double-click on a name to change the first form to that person.
• Allow entries on multiple worksheets by including on your form a dropdown list of the sheet names. For example, you might have one sheet called “personal” and a second sheet called “work”. The dropdown list on your form would include each of these sheet names. By selecting a different sheet from the dropdown, the form would switch to show the set of names/numbers from that worksheet.

If you’re feeling ambitious, you might try some of these things. Being the big programming geek I am, I actually created a spreadsheet project like this a couple of years ago and have kept all of my personal contact information in it ever since. If you have any questions, or suggestions for future articles, please feel free to drop me a line: thorpe@ntsource.com. Hope you have a wonderful summer!

Need further help getting your Excel VBA code right? Join our free Excel VBA support group! See this link for details: http://groups.yahoo.com/group/ExcelVBA/.