Software

​Office Q&A: How to update UserForm VBA code to accommodate an Excel Table

VBA easily accommodates input values; you can copy them from a UserForm to a data range or a Table with only a few differences in the code.

hero
The article How to add a UserForm to aid data entry in Excel uses VBA to copy input values from a UserForm to a sheet (data range). Adam wants to insert input values into an existing Table instead of a data range. Fortunately, accommodating a Table requires only a few changes to the code. In this article, I'll show you how to use VBA to copy input values from a UserForm to a Table object.

I'm using Excel 2016 on a Windows 10 64-bit system but Excel 2007 and up support the Table object. For your convenience, you can download the demonstration Excel .xlsm and .frm files.

Setup

We'll need three things:

  • A Table to store the input values at the sheet level
  • A UserForm to solicit the input values from the user
  • Some VBA code to copy the input values from the UserForm to the Table

Figure A shows the simple Table and UserForm we'll create in this example.

Figure A

Figure A

We'll use this UserForm to populate the Table.

It's important to note that the sheet is named Animals. You don't have to rename the sheet, but be sure to update the code appropriately if you don't. In addition, make sure you're working in a macro-enabled workbook (*.xlsm).

SEE: How to use Word's Outline view to quickly sort long lists

The Table

Creating a Table is simple. If you already have a few rows of data (as Adam does), do the following:

  1. Click any cell inside the data range.
  2. Click the Insert tab.
  3. Click Table in the Tables group.
  4. Check the My table has headers option (Figure B) if necessary and click OK.
  5. Next, name the table by clicking the contextual Design tab and entering AnimalTable as shown in Figure C.

Figure B

Figure B

Accommodate headers in your Table.

Figure C

Figure C

We'll use the name when referencing the Table in code.

The UserForm

At this point, you have a Table at the sheet level for storing the data. Your next step is to create the UserForm as follows:

  1. Open the Visual Basic Editor (VBE) by pressing [Alt]+[F11].
  2. From the Insert menu, choose UserForm.
  3. Name the UserForm ufrmAnimals using the Project Explorer to the left.
  4. Using Figure D as a guide, insert the controls; refer to Table A for their names.
  5. Add labels for descriptive captions; add description captions for the command buttons.

Table A

Table A

Figure D

Figure D

Insert and name the controls.

The code

The code behind the Save Animal (cmdAdd) command button does most of the work. To add the code, double-click the UserForm to open its module and enter the procedures shown in Listing A. (Don't try to copy and paste from this web page because the VBE will object to some web characters. Instead, download the example files.)

Listing A

Private Sub cmdAdd_Click()
    'Copy input values to sheet.
    Dim oNewRow As ListRow
    Dim rng As Range
    Set rng = ThisWorkbook.Worksheets("Animals").Range("AnimalTable")
    rng.Select
    Set oNewRow = Selection.ListObject.ListRows.Add(AlwaysInsert:=True)
    With ws
        oNewRow.Range.Cells(1, 1).Value = Me.cboClass.Value
        oNewRow.Range.Cells(1, 2).Value = Me.txtGivenName.Value
        oNewRow.Range.Cells(1, 3).Value = Me.txtTagNumber.Value
        oNewRow.Range.Cells(1, 4).Value = Me.txtSpecies.Value
        oNewRow.Range.Cells(1, 5).Value = Me.cboSex.Value
        oNewRow.Range.Cells(1, 6).Value = Me.cboConservationStatus.Value
        oNewRow.Range.Cells(1, 7).Value = Me.txtComment.Value
    End With
    'Clear input controls.
    Me.cboClass.Value = ""
    Me.txtGivenName.Value = ""
    Me.txtTagNumber.Value = ""
    Me.txtSpecies.Value = ""
    Me.cboSex.Value = ""
    Me.cboConservationStatus.Value = ""
    Me.txtComment.Value = ""
End Sub

Private Sub cmdClose_Click()
    'Close UserForm.
    Unload Me
End Sub

Private Sub UserForm_Initialize()
    'Populate class control.
    With Me.cboClass
        .AddItem "Amphibian"
        .AddItem "Bird"
        .AddItem "Fish"
        .AddItem "Mammal"
        .AddItem "Reptile"
    End With
    'Populate conservation status control.
    With Me.cboConservationStatus
        .AddItem "Endangered"
        .AddItem "Extirpated"
        .AddItem "Historic"
        .AddItem "Special concern"
        .AddItem "Stable"
        .AddItem "Threatened"
        .AddItem "WAP"
    End With
    'Populate sex control.
    With Me.cboSex
        .AddItem "Female"
        .AddItem "Male"
    End With
End Sub

The UserForm_Initialize() procedure populates the three combo box controls when you run the form. This code is different from that of the original article and warrants a short discussion. In the original article, I used each control's DropButtonClick event. For example, you might use the following code to populate the class control:

Private Sub cboClass_DropButtonClick()
    'Populate class control.
    Me.cboClass.Clear
    With Me.cboClass
        .AddItem "Amphibian"
        .AddItem "Bird"
        .AddItem "Fish"
        .AddItem "Mammal"
        .AddItem "Reptile"
    End With
End Sub

It's basically the same code with one exception—I added a Clear method. Without it, the procedure duplicates the list every time the user clicks the dropdown button during the same session. If you need to populate using a similar dropdown procedure, be sure to add the Clear method to avoid duplicates.

The last procedure, cmdClose_Click(), closes the UserForm. Most of the work is done in cmdAdd_Click(), which copies the input values from the UserForm to the AnimalTable Table and then clears the controls so you can enter another record.

Now let's discuss the differences in code between the first article and this Table solution. The ListRow collection in the Dim statement

Dim oNewRow As ListRow

might be new to you. This collection contains all the rows in a list object, and under the hood, a Table is a list. The statement

Set rng = ThisWorkbook.Worksheets("Animals").Range("AnimalTable")

defines the rng object as AnimalTable and then

rng.Select


selects AnimalTable. The next statement

Set oNewRow = Selection.ListObject.ListRows.Add(AlwaysInsert:=True)

allows the selected list (our Table, AnimalTable, in this case) to insert a new row.

The With statement doesn't use the structured referencing you probably associate with the Table object. In this case, I believe the oNewRow variable is more efficient because one statement inserts the input values as a new record and tells Excel where the values go.

Use the UserForm

To easily run the UserForm, add the procedure shown in Listing B to open the UserForm. Don't add this procedure to the UserForm's module. Instead, choose Module from the Insert menu and add the short procedure. Then, add a macro button to the Quick Access Toolbar (QAT). If you don't know how to do this, read How to add Office macros to the QAT toolbar for quick access.

Listing B

Sub ShowAnimalsUF()
    'Display Animals UserForm.
    ufrmAnimals.Show Modal
End Sub

To display the UserForm, click the macro button on the QAT (or run it from the Developer tab if you don't want to add a macro button). Enter values for a new animal, as shown in Figure E. To add the new animal record to AnimalTable, click the Save Animal button. Figure F shows the updated Table.

Figure E

Figure E

Add a new record.

Figure F

Figure F

The UserForm 's code adds the new record to the Table.

Worth noting

When creating a UserForm, be sure to add adequate error-handling code; this example contains no error handling. In addition, the code doesn't save the workbook. You can add the necessary code to the UserForm or you can let users decide when and if to save the file. Undo won't remove a newly copied record.

Learn more about Excel's Table object:

Send me your question about Office

I answer readers' questions when I can, but there's no guarantee. Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.

Also read...

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox