cfo.com

Print this article | Return to Article | Return to CFO.com

Spreadsheets: Letting Users Fill In the Blanks

Using ControlSource is an easy – but not effective – way to create a userform. Try instead the macro described in this article, which allows you to fill in new data without wiping out the old data.
Bill Jelen, CFO.com | US
April 27, 2011

Editor's Note: To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com's Spreadsheet Tips Page.

Reader Ebrima S. wins an e-book copy of Office VBA Macros You Can Use Today from CFO and MrExcel.com for his question, "I created VBA Useform for data entry into a table in a worksheet. My intention is for users to be able to fill sales information in the table. However I realize that anytime I input new data, the newer data always replaces the earlier data. In other words, all the input data always drops in one row. How do I make sure the new input data moves to a new row (a new line)?"

Microsoft has a few demos floating around that show you how to create a userform and how each textbox is specifically tied to a cell using the property called ControlSource. While these demos make it seem easy to create a userform, the problem is the one described by Ebrima in this question.

Figure 1 (below) shows a small custom userform in Excel. Figure 2 shows the ControlSource property for the Invoice Number textbox. Figure 2 indicates that the value for the textbox comes from Sheet1!A2 and will be written back to Sheet1!A2.

 

Fig. 1

MrExcel4-27Fig01

 

Fig. 2

MrExcel4-27Fig02

 

The problem: using ControlSource is not an effective way to create a userform. It certainly is an easy way, but since each textbox always is tied to a single cell, it is very inflexible. Here's how to fix the problem.

Delete the ControlSource
In the VBA window, make sure you can see the Properties pane by using View, Properties Window or pressing F4. Then click on each textbox, listbox, dropdown in your userform and blank out the ControlSource for each item. Also, after clearing out the ControlSource, clear out the leftover values for those fields. In the future, you are going to write a tiny bit of code that will write the values from the userform fields to the next row on the worksheet whenever anyone clicks the OK button.

How to Add Code to the OK Button
Right-click the OK button and choose View Code as shown in Figure 3. This will take you to a blank procedure that looks like this:
    Private Sub CommandButton1_Click()
    End Sub

Fig. 3

MrExcel4-27Fig03

Type your code inside of that procedure. The first task is to figure out the last item in column A that has an invoice number. The code is:
    NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1

Rows.Count is a very clever way to figure out if the current worksheet has 65,536 rows or 1,048,576 rows. Putting Rows.Count inside of the Cells function essentially asks Excel to start at A1048576, then press the End key, then the up arrow key, then figure out what row that would be. The code adds 1 to get to the next open row. This is assigned to a variable called NextRow.

Once you know the location of the first blank row, you have one line of code for every textbox to write that textbox value back out to the worksheet. Here is the NextRow formula:


    Cells(NextRow, 1).Value = Me.TextBox1.Value
    Cells(NextRow, 2).Value = Me.TextBox2.Value

At this point, you have a decision. Will someone be entering a single invoice and then stopping? If so, close the userform with:
    Unload Me

If people will be entering many invoices, you can clear out the textboxes and leave the userform open for them to continue. In the following code, I cleared out the sales amount but incremented the invoice number by 1 in an effort to be helpful:
    Me.TextBox2.Value = ""
    Me.TextBox1.Value = Me.TextBox1.Value + 1

Also, if you want the person entering data to see the row where the next value will go, you can select that row:
    Cells(NextRow + 1, 1).Select

Give Them a Way to Close the Form
If you decide to keep the form open after pressing OK, you need a way to close the form without saving. I use the Cancel button for this, with this code:
  Private Sub CommandButton2_Click()
     ' Close the form
     Unload Me
  End Sub

Do You Need to Adjust Some Values Before Showing the Form?
This next step is completely optional. In the code attached to the OK button, you incremented the invoice number for the person so he would not have to type it. You could load up the next invoice number before displaying the form. Use this code:
  Private Sub UserForm_Initialize()
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Me.TextBox1.Value = ""
    Me.TextBox2.Value = ""
    If LastRow > 1 Then
      Me.TextBox1.Value = Cells(LastRow, 1).Value + 1
    End If
  End Sub


The Complete Code in the Userform
Putting all of the above together, you have three procedures stored in the userform.

The first:
 Private Sub UserForm_Initialize()
     LastRow = Cells(Rows.Count, 1).End(xlUp).Row
     Me.TextBox1.Value = ""
     Me.TextBox2.Value = ""
     If LastRow > 1 Then
        Me.TextBox1.Value = Cells(LastRow, 1).Value + 1
     End If
  End Sub

  The second:
  Private Sub CommandButton1_Click()
     NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
     Cells(NextRow, 1).Value = Me.TextBox1.Value
     Cells(NextRow, 2).Value = Me.TextBox2.Value

     ' Clear out the text boxes?
     Me.TextBox2.Value = ""
     Me.TextBox1.Value = Me.TextBox1.Value + 1

     ' Select the next row to give a visual?
     Cells(NextRow + 1, 1).Select
  End Sub

  The third:
  Private Sub CommandButton2_Click()
     ' Close the form
     Unload Me
  End Sub

Using the Form
When the form is first displayed, the invoice number will be prefilled with the first invoice number (Figure 4).

Fig. 4

MrExcel4-27Fig04

 

Type 1500 for the invoice amount. Notice that unlike the ControlSource userform, the userform in Figure 5 has not changed anything in the worksheet yet.

Fig. 5

MrExcel4-27Fig05

 

After you click OK, the values from the form are written to the next row (Figure 6). The form stays open with the next invoice number as the default value in the invoice number field. Admittedly, writing 20 lines of code takes longer than using the ControlSource property; however, the flexibility and benefits far outweigh the extra time required to add the macro code.

Fig. 6

MrExcel4-27Fig06

 

CFO contributing editor Bill Jelen is the founder of MrExcel.com and author of 32 books about Excel, including three editions of VBA & Macros for Microsoft Excel. As always, you can win a copy of one of his books if your question is selected for a column. Post your question to the community content block on the right.




CFO Publishing Corporation 2009. All rights reserved.