Fun with Data Forms in Microsoft Excel 2007

Published on
8,828 Points
3 Endorsements
Last Modified:
So.  You want to input a lot of data into Excel and you feel like having a more user friendly way of entering it?
Well here is a case for Data Forms!!!
What do you need to do?  It's simple :)


Make a list of all the inputs in one row.
For example: A1 = Author, B1 = Date, C1 = Procedure, etc.
Once you have completed entering all your input titles, step 2!


Look at the top left of Excel 2007.  You see the windows button, and to the right of that, you see a floppy disk to save, some arrows, and on the right of these arrows(Undo Clear), you most likely will have nothing but a dropdown box indicator that looks like an arrow pointing down. Click on this and and it should have a list saying "Customize Quick Access Toolbar".
Slowly scroll down until you see "More commands."  Click on it.


After this,  a menu will pop up.
On the top, under "Choose Commands From:", you must select "All Commands"


From here, click on the box below and search for "Form..."  Once you have found it, click "Add>>" and then click OK on the bottom of the menu.


You will be returned back to your simple Excel file, with the inputs, except now at the top of the Quick Access Toolbar, you will notice the new Form... button!  :)


Click on your A1 cell, where you may have entered "Author," as I previously suggested in step 1.  Next, click on the form button.  Then a box should come up.  Click  OK.


Now you have your form!
Feel free to enter your information and then hit New to move onto a New line!


You can close this box and return to it by the same process.  When you have closed it and opened it again, you can always look through your entries by scrolling through the data form, and enter new entries using the New button!  Also, you can even use the "Criteria" button as a search filter of sorts.


After this stage, if you like, you can add a button on the top of your worksheet which automatically pulls up the dataform with respect to A1.  This helps a lot if you are creating this for other people to use and you don't have the time to explain to them how to add the Form... button.  All they will have to do most likely is enable your Macro(s)
The VBA code is as simple as:
Sub DataForm()
' DataForm Macro
' Opens dataform!

End Sub

Open in new window

I have attached an Excel spreadsheet (Note: You must enable macros in order to see it in action).
Hope this helps :)
Best Regards,
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free