Creating, Configuring and Using Simple Forms in Excel 2007 and Excel 2010

Hi and welcome to my latest two part hub on Excel. Today in Part One, I will be looking at creating simple forms using the Form button. This hub came about as a result of seeing that someone did a search for a simple form and ended up looking at my hub on List Boxes. Curiosity then did what curiosity does and I ended up here writing about Forms in Excel.

Forms are perfect for those situations where you need to enter data into the same columns multiple times quickly and easily and with the minimum of effort. In Excel 2007 and Excel 2010 you can create and use a simple form using column labels you choose and via this form you can then enter records directly into a spreadsheet. You can also return to the form with a simple click of the mouse (by selecting any cell in the form and then clicking the Form button you can immediately continue to edit existing records as well as adding or deleting existing ones). You can see an example of a simple form I created in the figure below.

You can also create a form using List Boxes. I will be looking at creating a form using List Boxes in Part Two of this hub which can be found here:

http://robbiecwilson.hubpages.com/hub/Configuring-and-Using-Forms-created-using-ActiveX-List-Boxes-in-Excel-2007-and-Excel-2010

The main difference between a Simple Form and a List Box created Form is:

• With a List Box Form you select items from the lists provided
• Using a Simple Form you type items into the form’s dialogue box which are then entered into your Excel spreadsheet.

I have also created a command button which using Visual Basic copies the record and inserts it to the row below. To create the Visual Basic code for the command button, I recorded a macro.

If you are interested in finding out more about recording a macro and then using this code for a command button as well as List Boxes, I have a hub that goes into further detail on recording macros which can be found here

http://robbiecwilson.hubpages.com/hub/Guide-to-recording-Macros-in-Excel-2007

and a hub that covers creating and working with command buttons that can be found here: http://robbiecwilson.hubpages.com/hub/Create-a-Command-Button-in-Excel-2007 .

I also have two hubs regarding both Form Controls and ActiveX Controls List Boxes which can be found here

http://robbiecwilson.hubpages.com/hub/How-to-Use-Create-and-Configure-Form-Controls-List-Boxes-in-Excel-2007-and-Excel-2010 and here

http://robbiecwilson.hubpages.com/hub/Using-creating-and-configuring-ActiveX-Controls-List-Boxes-in-Excel-2007-and-Excel-2010

First we need to add the Form button which is by default not visible in Excel 2007 and Excel 2010.

In Excel 2007:

• Click the Excel button
• Select Excel Options
• Click Customize
• Now we will Customize the Quick Access Toolbar
• Select All Commands from Choose commands from
• Scroll down to Form and click the Add button

• Click OK

The Form button will now be added to your Quick Access Toolbar in Excel as shown below.

In Excel 2010 the process is almost identical to that of Excel 2007. To add the Form button in Excel 2010:

• Select File then Options
• Select the Quick Access Toolbar tab
• Select All Commands from Choose commands from
• Scroll down to Form and click the Add button

For both versions of Excel you can also do this by clicking the arrow to the right of the Quick Access Toolbar

Now that we have added the Form button, we need to create and configure a simple form.

First create your column labels

• Select the first column label
• Click the Form button

If you don’t have any data below your labels, you will receive the following error

• Click OK if you receive the above error
• Now go ahead and fill in the fields

• Click New and Excel will create a new record and place it on your Worksheet under the column headers
• To move between records, you can use the Find Prev and Find Next buttons
• To delete a record, select the Delete button
• Once you have finished adding records, click the Close button

Note: If you want to add more records to your form, click anywhere on the form and press the Form button.

Note: to amend a record, use Find Prev or Find Next to select the record, amend it and then click New to commit the change to that record.

The Form (also called the Simple Form) in Excel allows you to simply and easily create a form based on the labels you create. Using the form you can add / remove or edit records and Excel 2007 / Excel 2010 will add these records to your spreadsheet.

This will allow you to quickly and easily enter data into a spreadsheet in a neat and orderly manner. In Part Two of this hub, I will create a similar form using List Boxes. In addition, I will create a Command Button which copies the data created by the List Boxes to a new row. The Command Button uses Visual Basic which I created by recording a macro and using the output to create my Visual Basic code. Part Two can be found here:

http://robbiecwilson.hubpages.com/hub/Configuring-and-Using-Forms-created-using-ActiveX-List-Boxes-in-Excel-2007-and-Excel-2010

Many thanks for reading Part One, I do hope that you found it useful and that you will be able to now use Forms in Excel to assist you with your day to day work. Please join me in Part Two as we create a simple form using List Boxes. If you have any comments, feel free to leave these below.