How to Capture Data From UserForms Into an Excel Worksheet

by Warren Davies

With Excel, you can use Visual Basic to add a range of different features to your spreadsheets, including “UserForms.” Through UserForms, you can display an input form to the user that is separate from the main worksheet, which is useful for capturing data from individuals that may not be familiar with spreadsheets. With a little Visual Basic code, you can add all of this data to your spreadsheet in new rows.

Define Your Fields

Launch Excel. Enter the names of all the fields you want to capture on the top row, starting from “A1” and working to the right. Click “Developer,” and then click “Visual Basic.” The Visual Basic window will appear. Click the “A” icon from the Toolbox, and click and drag inside the “UserForm1” window at the location you want your first form field to go. Edit the label by changing the “Caption” property into something more descriptive, such as “First Name," for example. Create a label for each of the fields you entered on your worksheet.

Add Your Text Boxes

Select the “TextBox” icon from the Toolbox, and drag out a text box just to the right of your first label. If you'll only be using a few fields, it is acceptable to leave the “(Name)” property as the default “TextBox1.” However if you need a large number of fields, this could get confusing, so you should edit these to match the labels you have already created. Make sure each label has a corresponding text box.

Add a Submit Button

Click the “Command Button” icon in the Toolbox, which looks like a standard Windows-style button. Click and drag it to the size and location you require in UserForm1. Typically, you'll want this below or near the final text box in your form. Change the button's “Caption” property to “Submit Data” or something similar, indicating to the user that they must click this button to submit their data.

Add Visual Basic Code

Double-click the button. Enter the following code between “Private Sub” and “End Sub”: eRow=Sheet1.Cells(Rows.Count,1).End(xlUp).Offset(1,0).Row Then start a new line beneath this one and then enter the following: Cells(erow, 1) = TextBox1.Text If you renamed your text boxes, change “TextBox1” to the name you gave the first text box. Do this for each input box you created, starting a new line and entering the correct text box names as appropriate. Also, for each new line, increment the figure in the parentheses by one. For example, the next line might look like this: Cells(erow, 2) = TextBox2.Text Click the “Play” button at the top of the window to start capturing data.

About the Author

Warren Davies has been writing since 2007, focusing on bespoke projects for online clients such as PsyT and The Institute of Coaching. This has been alongside work in research, web design and blogging. A Linux user and gamer, warren trains in martial arts as a hobby. He has a Bachelor of Science and Master of Science in psychology, and further qualifications in statistics and business studies.

Photo Credits

  • photo_camera Creatas Images/Creatas/Getty Images