Excel Data Entry and Update FormWith this Excel data entry form, you can enter or update records on the data entry worksheet (named Input). This make user input easier, and helps prevent input errors. Tip: If you want to build a pop-up form, go to the steps for building a basic Excel UserForm. There is also a built-in Data form that you can use - with no programming required. Get the steps for finding and using that hidden Data Entry form. |
Excel Data Entry and Update Form OverviewThanks to Dave Peterson, who created the first version of the data entry form. With this Microsoft 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:
|
Enter New DataTo enter a record:
|
|
|
|
historyWks.Range(historyWks.Cells(lRecRow, 3), _ historyWks.Cells(lRecRow, 6)).Copy
|
.Cells(nextRow, 3).PasteSpecial _ Paste:=xlPasteValues, Transpose:=True
Add New Input AreasIn the sample workbooks available to download, one of the files has two input areas, and additional sheets that are linked to those input areas. |
If you need more input areas, follow the steps below, and use the setup and naming structure for the existing input areas as a guide. In this example, a third input area is being added:
|
If you change the sheet names in your copy of the Worksheet Data Entry Form workbook, follow these steps to modify the macro code, so it will recognize the revised sheets.
NOTE: Watch these steps in the How to Change Sheet Names in the Code video, above.
REMINDER: Make a backup copy of your workbook, before changing the macro code, or making any other major changes.
To see the macro code, right-click on the Input sheet tab, and click View Code At the left, in the Project Explorer, the workbook name is listed, with folders that contain its Excel objects and code modules. |
Sheet NamesFor each worksheet, there is a Code name, followed by the name that you see on the sheet tab (in brackets). For example:
In the Worksheet Data Entry Form sample file, the sheet tab names are used in the macro code. For example: |
Set inputWks = Worksheets("Input") Set historyWks = Worksheets("PartsData")
Choose a Macro Update OptionIf you change the sheet tab names, use one of these options for changing the names in the macros:
Later, if the sheet tab names are changed again:
Start the UpdateFor either option, follow these steps to get started:
|
Follow the Option StepsNext, follow the stops for the option that you're using:
Option 1 - Change All Sheet Tab Names in MacrosIf you are using option 1, follow these steps. Warning: Be sure to include the double quotes, or other parts of the code might be accidentally replaced.
|
Option 2 - Change Macros to use Code NamesIf you are using option 2, follow these steps TIP: You can select the code on the worksheet, and Ctrl+C to copy it. Then, use Ctrl+V in the Replace Dialog, to paste the text.
|
Database Start RowIn most of the sample workbooks, the parts database starts in row 1 on the PartsData sheet. The code calculates record numbers and last record number, by adding a 1 to the row number. If your database headings are NOT in row 1, use the Set Data Start Row sample file (#5 in the download section below). In that workbook, go to the modData code module, and change the DataStartRow setting, to match the row where your data headings are located. |
Get the WorkbooksThe sample files are in xlsm format, and are zipped. After you unzip the file and open it, enable macros, so you can use the worksheet buttons. 1. Data Entry Form - Add/Update: View, edit and add records. 2. Data Entry Form With Delete. Add, Update, New and Delete buttons. Checks database for Order ID, to prevent duplicates. Use this version with caution, because the deleted records are gone forever. Click here to read the details on this version 3. Data Entry Form With Delete and Limit. Like the Delete version above, but with a limit on the items stored per location. Only the available locations are shown in the drop down list. 4. Data Entry Form - 2 Entry Sections: Data is entered in two areas, and copied to and from these cells by using links on other worksheets. If required, use the same technique to add more data entry ranges and fields in a workbook. 5. Data Entry Form - Add/Update - Set Data Start Row: View, edit and add records. Change the DataStartRow setting, if your data headings are in a different row. 6. Data Entry Form With Delete and Alert. Like the Delete version (2) above, but with alerts when new or updated data is entered. Data entry labels change colour, and message appears below buttons, reminding people to click Add or Update. More Versions: For more versions of the Worksheet Data Entry form, go toinstructions on how to build a basic Excel Data Entry Form on a worksheet. |
Last updated: July 17, 2022 7:39 PM