Easy Excel Data Entry Form

​By Terry McCamish, Accountability Data Analyst, Office of Technical Education Accountability & Institutional Effectiveness Division, Technical College System of Georgia 

I was tasked with collecting budget information from the 23 colleges in the Technical College System of Georgia. Each spreadsheet had college-specific information, as well as common budget items. The time frame was short to collect the data, but I wanted the data collection to be as painless as possible. 

The data needed to be collected in a spreadsheet to have maximum flexibility to analyze the data, but I didn’t have a survey tool that would accomplish everything I wanted. With programming or VBA skills, you could build a UserForm in Excel to gather your data, or you could build something directly in a spreadsheet. The spreadsheet I created, however, makes use of macros recorded in Excel and doesn’t require extensive programming skills.

I had 20+ data items to collect, but sending the spreadsheet with those items in columns would have made for clumsy data entry. I wanted the spreadsheet to look like a form (it could even be printed on one page and distributed that way), but I needed to be able to manipulate the final product.

The workbook requires at least two worksheets; the form itself and a summary page. My workbook also has a page for instruction and definitions, as well as some hidden worksheets for data validation on the form. 

I am not a VBA programmer, but I knew what I wanted to happen. First, I wanted to copy the cells where the data would be entered, then I wanted to copy and paste that data in the next column and move that data one column to the right. Next  I wanted to clear the entered data from the form and go back to the first data entry cell. On the Excel Developer tab (which is not enabled by default in Excel 2010 and later), there is a Record Macro button. I knew if I could perform the tasks correctly without a lot of bad clicks (harder than it sounds), Excel would create my VBA code automatically. This recorded macro would be attached to the Add/Save button (another option on the Developer tab). It is worth enabling this tab and learning what is available.

Once I had the data in columns, I needed another macro to transpose the data, which is accomplished with the Create Summary button.

The macro put the cursor in the first cell of the copied data, in this case, cell C2. Then I selected everything from that cell to the end of the spreadsheet. (I do this manually; Ctrl-Shift-End is a handy shortcut.) Once all the data was selected and copied, I went to the Summary Worksheet to cell A2. I preloaded my column headings beforehand. Then, all that needed to be done was to transpose and paste the data.

I placed another button on the form called Create Summary and attached my new macro. This is how the form looks:

dataform1.JPG

This is the VBA created by Excel’s Macro recorder:

dataform2.JPG 
The code copies whatever has been entered (Lines 3-5) to the next column (Line 6), inserts cells and moves the copied data one column to the right (Line 7) (so the data doesn’t get copied over), selects the form range (Line 8), resets the cells for more data (Line 9), and moves the cursor back to the first data entry cell (Line 12). This is what happens when the Add/Save button is clicked (it does not perform a file save).
 
When the data entry is complete, the result is a spreadsheet with the data in rows. However, because it is easier to manipulate the data in columns, we use the Create Summary button. The VBA associated with this button looks like this:
  

dataform3.JPG 

The purpose of the VBA is to take everything entered and copy the data to a different worksheet and transpose it. Line 3 moves to the first cell with data, Line 4 selects everything from the beginning of the data to the last cell in the worksheet, Line 5 copies the cells, Line 6 selects the other worksheet, Line 7 locates the first cell in that worksheet, and Line 8 pastes the data and transposes it. The last three lines go back to the form worksheet and to the first data entry cell. The Summary button can be utilized as many times as necessary.
 
The finished product is a worksheet with the collected data residing in rows instead of columns. Now I was able to filter by the various categories, and total the cost columns.
 
The form was saved as an Excel Macro-Enabled Workbook file (.xlsm). The basic template was saved and then modified with each college’s specific information and sent via email. The colleges collected the data, sometimes by sending the file to individual departments, and sent it back to me. It was simple to cut and paste the individual summary pages to a new file. Having the data in this format certainly simplified my work in compiling the data for 23 colleges. Please see my template file for reference. (Note: Save the file to your computer to view).
 
Some Excel VBA Sources include Chandoo.org, Contextures, MrExcel, and Excel Daily News.

 

 

 Comments

 
To add a comment, Sign In
Total Comments: 3
 
Lee posted on 4/9/2015 2:47 PM
Thanks Terry! This is a great idea for creating an easy form input within Excel.
Keith posted on 4/9/2015 2:52 PM
This tip will help us going forward to create more complex survey response forms that integrate better with our analysis process. Thanks for sharing!
Joe posted on 5/5/2015 5:44 PM
Cool... you can tweak this to get some nice formatting! I've been using
Options/Customize ribbon/All Commands/Form...
but formatting it requires VBA. This is a nice idea!

 

 

 

​ ​​​​​​​