Data Entry Worksheet Form

Use a worksheet form for Excel data entry, and the VBA code automatically saves the entry to a list on a different worksheet in the same workbook.

Worksheet Data Entry Form Overview

Thanks to Dave Peterson, who wrote this technique.

Use this simple worksheet form for Microsoft Excel data entry.

• On the Input worksheet, enter data in the green cells
  • Select Item and Location from data validation drop down lists
• After you select an Item from the drop-down list, its price is automatically entered
  • VLOOKUP formula returns price from a lookup table, on LookupLists sheet.
• Next, click the Add to Database button, to save entry to list on PartsData worksheet in the same Excel workbook.

Add to Database Button

When you click the Add to Database Button, it runs an Excel macro named UpdateLogWorksheet. You can see the full macro code in the VBA Code section, below

The UpdateLogWorksheet macro does the following steps:

• Checks to see if all the input cells have something in them
  • If any values are missing:
    • Macro shows a warning message - "Please fill in all the cells!"
    • Macro stops running, and data is not added to database sheet
• If all input cells are filled, data is added to next blank row in database sheet
• Current date and time is automatically added to column A of database sheet
• Username (taken from Excel Options, General category) is added to column B of database sheet
• Data is cleared from non-formula cells on Input sheet
• The first data entry cell is selected, ready for the next parts order details

NOTE: For a version of the Worksheet Data Entry form that allows you to select and edit existing records, please see Forms -- Excel Data Entry and Update Form

View Database Button

When you click the View Database button, it runs an Excel macro named GoInventory. Here is the code for that macro. It simply activates the worksheet named PartsData, where the data is stored.

Sub GoInventory()
  On Error Resume Next
  Worksheets("PartsData").Activate
End Sub

Input Sheet Button

On the PartsData sheet, there is an Input Sheet button, which takes you back to the Input sheet.

hen you click the Input Sheet button, it runs an Excel macro named GoInput. Here is the code for that macro. It simply activates the worksheet named PartsData, where the data is stored.

Sub GoInput()
  On Error Resume Next
  Worksheets("Input").Activate
End Sub

Modifying Worksheet Data Entry Form

All the values from the Excel data entry form are then stored on the summary sheet (database), starting in Column C, in the same order as the addresses you've specified in this line:

myCopy = "D5,D7,D9,D11,D13" 

You can change those references to match your input worksheet layout.

Clearing Input Cells

The macro clears some of the cells in the Input worksheet

• All the data entry cells that contain constant values are cleared at the end of the code, for the addresses you've specified in the myCopy string.
• Any cells with formulas are left untouched.

Getting Started With Macros

To see the steps for pasting a macro into a workbook, and running the macro, go to the Copy Code to an Excel Workbook page. That page has videos and written steps that show what to do.

For example, this video shows the steps for pasting a macro into a workbook, and running the macro.

For more information on getting started with Microsoft Excel macros, you may want to read David McRitchie's intro at: https://www.mvps.org/dmcritchie/excel/getstarted.htm

Worksheet Data Entry Form VBA Code

Here is the full VBA code for the UpdateLogWorksheet macro, which runs when you click the Add to Database button.

Sub UpdateLogWorksheet()
'https://www.contextures.com/xlForm02.html
'code by Dave Peterson

    Dim historyWks As Worksheet
    Dim inputWks As Worksheet

    Dim nextRow As Long
    Dim oCol As Long

    Dim myRng As Range
    Dim myCopy As String
    Dim myCell As Range
    
    'cells to copy from Input sheet - some contain formulas
    myCopy = "D5,D7,D9,D11,D13"

    Set inputWks = Worksheets("Input")
    Set historyWks = Worksheets("PartsData")

    With historyWks
        nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
    End With

    With inputWks
        Set myRng = .Range(myCopy)

        If Application.CountA(myRng) <> myRng.Cells.Count Then
            MsgBox "Please fill in all the cells!"
            Exit Sub
        End If
    End With

    With historyWks
        With .Cells(nextRow, "A")
            .Value = Now
            .NumberFormat = "mm/dd/yyyy hh:mm:ss"
        End With
        .Cells(nextRow, "B").Value = Application.UserName
        oCol = 3
        For Each myCell In myRng.Cells
            historyWks.Cells(nextRow, oCol).Value = myCell.Value
            oCol = oCol + 1
        Next myCell
    End With
    
    'clear input cells that contain constants
    With inputWks
      On Error Resume Next
         With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
              .ClearContents
              Application.Goto .Cells(1) ', Scroll:=True
         End With
      On Error GoTo 0
    End With

End Sub

Add Navigation Arrows

If you would prefer to view the records on the data entry sheet, instead of going to the database sheet, use Version 2 of the worksheet Data Entry Form. You can get that version in the Download section, below.

Data Entry Form Version 2 has navigation arrows added, so you can scroll through the records, to see the data in each one.

• The navigation buttons take you to the first, previous, next or last record.
• Or, you can type a record number in the yellow cell, to go to a specific record in the saved data.
• The Go To Database button is still on the worksheet, but you could remove that, then hide the PartsData sheet, so users are less likely to change the data, accidentally or intentionally.

Navigation Arrow Macros

In Version 2, the navigation arrow buttons have macros assigned, and those run when you click an arrow button. Those four macros are stored in the module named modViewData:

• ViewLogFirst
• ViewLogUp
• ViewLogDown
• ViewLogLast

The four macros are similar, so here is the ViewLogDown macro as an example, and a brief description of how it works. This macro pulls data from the database, and enters it on the Input sheet

• Variables are set at the top of the macro
• Two worksheets will be used -- Input and PartsData.
• The last row is found on the PartsData sheet (historyWks)
• Then 1 is subtracted (for 1 heading row), to get the last record number (lLastRec). That will be the limit for scrolling down.
• Next, the current record number (lRec) is taken from the yellow cell (named "CurrRec")
• If lRec is lower than lLastRec, the macro adds 1 to the number in the yellow cell.
• Then, the macro finds the applicable row (lRec + 1) on the PartsData sheet
• Finally, the macro gets the values from columns 3, 4 and 5, and puts those values in in D5, D7 and D9 on the Input sheet.

Sub ViewLogDown()

    Dim historyWks As Worksheet
    Dim inputWks As Worksheet

    Dim lRec As Long
    Dim lRecRow As Long
    Dim lLastRec As Long
    Dim lastRow As Long
    Application.EnableEvents = False
    
    Set inputWks = Worksheets("Input")
    Set historyWks = Worksheets("PartsData")

    With historyWks
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        lLastRec = lastRow - 1
    End With

    With inputWks
        lRec = .Range("CurrRec").Value
        If lRec < lLastRec Then
            .Range("CurrRec").Value = lRec + 1
            lRec = .Range("CurrRec").Value
            lRecRow = lRec + 1
            .Range("D5").Value = historyWks.Cells(lRecRow, 3)
            .Range("D7").Value = historyWks.Cells(lRecRow, 4)
            .Range("D9").Value = historyWks.Cells(lRecRow, 5)
          End If
    End With
    Application.EnableEvents = True

End Sub

Get the Sample Data Entry Form

Original Version: Excel Data Entry Worksheet Form workbook. The zipped workbook contains macro, so be sure to enable macros if you want to test the data entry form.

Version 2 - Add/View: Data Entry Form (Add/View) Add new records, and use navigation arrows to scroll through the stored records, and view the data. The zipped workbook contains macro, so be sure to enable macros if you want to test the data entry form.

More Versions: For more versions of the Worksheet Data Entry form, go to Forms -- Excel Data Entry and Update Form. There are versions that let you delete records from the database, enter data in separate sections, limit the items stored per location, and other options.

Data Entry Forms Tutorials

Survey Form with Option Buttons

Print Selected Items in Order Form

Data Entry and Update Form

UserForm with ComboBoxes

UserForm Dependent ComboBoxes

UserForm ComboBox VBA