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.
Thanks to Dave Peterson, who wrote this technique.
Use this simple worksheet form for Microsoft Excel data entry.
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:
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
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
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
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.
The macro clears some of the cells in the Input worksheet
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
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
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.
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:
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
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. 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.
Last updated: March 14, 2022 8:22 PM