Search Contextures Sites
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.
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.
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.
If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm
The zipped sample Excel data entry form workbook, with the Excel VBA code, can be downloaded here: Excel Data Entry Worksheet 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
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
Last updated: April 13, 2014 2:27 AM
Contextures Inc., Copyright ©2014
All rights reserved.