ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

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

Updated on February 21, 2013

Introduction

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.

Example of a Simple Form created and configured in Excel 2007 and Excel 2010.
Example of a Simple Form created and configured in Excel 2007 and Excel 2010. | Source

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.

Example of a Form created using List Boxes, a Command Button and Visual Basic captured using a macro in Excel 2007 and Excel 2010.
Example of a Form created using List Boxes, a Command Button and Visual Basic captured using a macro in Excel 2007 and Excel 2010. | Source

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

Adding the Form button to 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

Adding the Form button to the Quick Access Toolbar in Excel 2007.
Adding the Form button to the Quick Access Toolbar in Excel 2007. | Source
  • Click OK

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

The Form button is now added to the Quick Access Toolbar in Excel 2007.
The Form button is now added to the Quick Access Toolbar in Excel 2007. | Source

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


Adding the Form button to the Quick Access Toolbar in Excel 2010.
Adding the Form button to the Quick Access Toolbar in Excel 2010. | Source

Creating and using a Simple Form in Excel 2007 and Excel 2010

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

Error received when creating a form without column labels selected in Excel 2007 and Excel 2010.
Error received when creating a form without column labels selected in Excel 2007 and Excel 2010. | Source
  • Click OK if you receive the above error
  • Now go ahead and fill in the fields

Example of a blank form created using Excel 2007 and Excel 2010.
Example of a blank form created using Excel 2007 and Excel 2010. | Source
  • 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.

Conclusion

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.

Example of a form with populated records created using Excel 2007 and Excel 2010.
Example of a form with populated records created using Excel 2007 and Excel 2010. | Source

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.

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)