![]()
Excel -- Worksheet Data Entry Form
Thanks to Dave Peterson, who wrote this technique.
Enter data on a worksheet form, and the following code will save the entry to a list on a different worksheet.
The code checks to see if all the input cells have something in them, and displays a message if values are missing.
The date/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.
Values from the data entry form are then stored, starting in Column C, in the same order as the addresses you've specified in this line:
myCopy = "D5,D7,D9,D11,D13"
Change those references to match your input worksheet.
Cells that contain constants are cleared at the end of the code, for the addresses you've specified in the myCopy string.
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 workbook can be downloaded here:
FormSheet.zip
Sub UpdateLogWorksheet() 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
1. Forms -- Survey Form with Option Buttons
2. Forms -- Worksheet Data Entry Form
3. Forms -- Print Selected Items in Order Form
Last updated: July 18, 2008 11:40 PM