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.
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()