Search Contextures Sites

Data Entry Worksheet Form

Worksheet Data Entry Form Overview
Modifying the Worksheet Data Entry Form
Getting Started With Macros
Download the Sample Data Entry Form
Edit Existing Entries in Worksheet Data Entry Form
Worksheet Data Entry Form VBA Code
Excel Data Entry Forms Tutorials

Thanks to Dave Peterson, who wrote this technique.

Worksheet Data Entry Form Overview

Use this worksheet form for Excel data entry, and the following Excel VBA code will save the entry to a list on a different worksheet in the same Excel workbook.

The Excel data entry code checks to see if all the input cells have something in them, and displays a message if any values are missing.

The date and time is automatically added to column A of the summary sheet and the username (taken from Tools | Options | General tab) is added to column B of the summary sheet.

Modifying the Worksheet Data Entry Form

All the values from the Excel data entry form are then stored on the summary sheet, 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.

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

If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm

Download the Sample Data Entry Form

The zipped sample Excel data entry form workbook, with the Excel VBA code, can be downloaded here: Excel Data Entry Worksheet Form

Edit Existing Entries in Worksheet Data Entry Form

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

Worksheet Data Entry Form VBA Code

Excel Data Entry Form

 

 

 

  

Sub UpdateLogWorksheet()
'http://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

   

Data Entry Forms Tutorials

1.Survey Form with Option Buttons
2. Data Entry Worksheet Form
3. Print Selected Items in Order Form
4. Data Entry and Update Form

 

Privacy Policy

 

Contextures Inc., Copyright ©2013
All rights reserved.

 

Last updated: May 30, 2013 3:33 PM