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.
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.
NOTE: 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
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
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
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. It 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 type a record number in the yellow cell, to go to a specific record.
The Go To Database button is still on the worksheet, but you could remove that, and hide the PartsData sheet, so users are less likely to change the data.
The navigation arrow buttons have macros assigned, and those run when a button is clicked. Those macros are stored in the module named modViewData -- ViewLogFirst, ViewLogPrev, ViewLogNext and ViewLogLast
The macros are similar, so here is the ViewLogDown macro as an example, and a brief description of how it works
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
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
For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.
Search Contextures Sites
Last updated: May 15, 2016 3:18 PM