• Technology
  • CFO.com | US

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.


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

Discuss

Your email address will not be published. Required fields are marked *