Search Contextures Sites

	Excel Data Entry and Update Form Thanks to Dave Peterson, who created the first version of the data entry form. Excel Data Entry Form Overview Data Entry Form Workbook Setup Enter New Data Navigate Through Stored Records Select a Specific Record Update a Record The Update Code Change Field Names Add New Fields Update the Navigation Code for New Fields Watch the Excel Data Entry Form Customize Video Download the Excel Data Entry Form Excel Data Entry Forms Tutorials Excel Data Entry and Update Form Overview With this Excel data entry form, you can enter or update records on the data entry worksheet (named Input). The form's features help you enter your data quickly and accurately: Drop down lists, created with data validation, help users enter the correct information. Make some fields mandatory, so users can't leave them blank. Click a button, to save the record and clear the data entry cells. Use navigation buttons to see the saved data Select a specific record to view or update. Data Entry Form Workbook Setup There are 3 worksheets used in the data entry and data storage process: Input - the data entry sheet. This can be protected, so users are only able to enter data in the unlocked cells. PartsData - the database sheet. This can be hidden, so users don't accidentally delete the data or change the layout LookupLists - item lists used for data validation drop down lists. Enter New Data To enter a record: Go to the data entry worksheet (named Input) Type a value in each mandatory data entry cell, or select from a drop down list The Price calculation is based on a VLOOKUP formula, and the Total formula multiplies the quantity by the price. Click the Add button, to copy the record to the database of stored records, located on the PartsData sheet. If mandatory fields are not filled in, a warning message will appear, and the record is not added to the database. If all the mandatory fields have been filled in, the data entry cells are automatically cleared, so you're ready to enter another new record. Navigate Through Stored Records To see the records that are stored on the database sheet, use the Navigation buttons. Click the navigation buttons to go to: First record |< Previous record < Next record > Last record >| Or, type a record number in the yellow cell, to view that record number Select a Specific Record Instead of scrolling through all the records, you can select a specific record, based on its ID number. In the sample workbook, the Order number is used as the ID number. Select the yellow cell under the "Select Order ID" heading. Click the drop down arrow, and select an ID number in the list Update a Record While you are viewing a record, you can change its data, then click the Update button to copy those changes to the database. For example, if you discovered that there was an error in the order quantity, you could change it. The Total formula on the Input sheet would automatically recalculate, to show the revised amount. When you're finished changing the record, click the Update button, and the revised data will appear in that record on the database sheet. The Update Code Before updating the database record, the Update code checks to see of all the mandatory data entry cells are filled in. If they aren’t, a message appears, and the code stops running. This prevents you from accidentally overwriting an existing record with blank cells. If all the data entry cells are filled in, the Update code: puts the current date and time in the selected record's row of the database adds the User Name from the Excel application copies the revised data to the database clears the data entry cells on the Input sheet Change Field Names After you download the sample Excel data entry form workbook, you can customize it by changing the field names, to match your data. On the data entry sheet, select a cell with a field name that you want to change Type a new name for the field Switch to the Database worksheet Make the same change to the field name in the column headings. Add New Fields If you need more fields that are in the sample Excel data entry workbook, you can add as many new fields as you need (up to the column limit in your version of Excel). On the data dntry sheet, unhide columns E:F. These contain markers and formulas, to control which fields are mandatory, and which are optional Select a label cell, and the 3 cells to the right, where you want to insert the new field. The new field will be inserted directed above the selected cells. Right-click on one of the selected cells, and click Insert. In the Insert window, select Shift Cells Down, and click OK In column F, use the Fill Handle, to copy the formula from the row above. If the new row copies the data validation from the row above, you can change it, to use a different list as the source. Or, clear the data validation, so users can type any value into the cell. On the PartsData sheet, insert a new column, between the same two fields where you added the new field on the Input sheet. In this example, the new field is inserted between the Part and Location columns. Add more fields, if you need them, and hide columns E:F when you're finished. Update the Navigation Code for New Fields If you add new fields, you'll need to change the range of cells that are copied, when the navigation arrows are clicked on the worksheet. To view the code for a navigation button, right-click on it, and click Assign Macro In the Assign Macro dialog box, click Edit. Look for the "With inputWks" section, and change the range that is being copied. In the screen shot below, there is a new field, so the 6 will be changed to 7. That will copy columns 3 to 7, instead of 3 to 6, so the new field will be included in the copied range. Make the same change in all four of the navigation macros: ViewLogFirst ViewLogUp ViewLogDown ViewLogLast Watch the Excel Data Entry Form Customize Video To see a quick overview of how you can customize the Excel Data Entry Form, you can watch this short video. Download the Excel Data Entry Form You can download a copy of the Excel Data Entry Form and customize it to suit your data. The file is in Excel 2003 format, and is zipped. After you unzip the file and open it, enable macros, so you can use the worksheet buttons. Excel Data Entry Forms Tutorials 1. Forms -- Survey Form with Option Buttons 2. Forms -- Excel Data Entry Worksheet Form 3. Forms -- Print Selected Items in Order Form 4. Forms -- Excel Data Entry and Update Form

Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact
Privacy Policy		Contextures Inc., Copyright ©2012 All rights reserved.	Debra Dalgleish

Last updated: January 3, 2012 4:15 PM