<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Fun with Data Forms in Microsoft Excel 2007

Published on
8,748 Points
2,448 Views
3 Endorsements
Last Modified:
Approved
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 :)

1

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!

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.

3

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

4

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.

5

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!  :)

6

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.

7

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

8

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.

9

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!
'

'
    Range("A1").Select
    ActiveSheet.ShowDataForm
End Sub

Open in new window


I have attached an Excel spreadsheet (Note: You must enable macros in order to see it in action).
SampleDataForm1.xlsm
Hope this helps :)
Best Regards,
Bordeaux0113
3
Comment
2 Comments
LVL 4

Author Comment

by:Bordeaux0113
Sample Worksheet!
Don't forget to enable macros.
VBA code is as simple as:

Sub DataForm()
'
' DataForm Macro
' Opens dataform!
'

'
    Range("A1").Select
    ActiveSheet.ShowDataForm
End Sub

SampleDataForm1.xlsm
0

Expert Comment

by:Rayne
Oh Bordeaux0113,

Thank you so much. This saved me hours of inventing wheels (form ideas) when input data set if complex

Thank you :)
0

Featured Post

Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Join & Write a Comment

This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month