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

       Home     Excel Tips     Excel Files      Blog    Contact

 

RSS Feed

 

 

 

The Excel Store

Last updated: July 18, 2008 11:40 PM