[VBA] How to Save UserForm Input Values as History | Auto-Save Date and Time

Background

When creating a UserForm with VBA in Excel, you often encounter situations where you want to “keep a record of what was entered in the form.”

For example, when someone registers data using a form, it is useful to automatically log:

• When it was registered.
• What content was entered.

This article explains how to implement a feature that accumulates input data as a history log.

Requirements

• Save text entered in the UserForm to an Excel sheet in an accumulative format.
• Automatically record the date and time alongside the data.
• When the button is clicked, the data is saved, and the form closes automatically.

Form Setup & Assumptions

• UserForm Elements:
  • Two TextBoxes: TextBox_1 and TextBox_2
  • One CommandButton: CommandButton_run
• Destination (Sheet1):
  • Column S: Value from TextBox_1
  • Column T: Value from TextBox_2
  • Column U: Registration Date/Time (Now)

Implementation Code (VBA)

Paste the following code into the code module of the UserForm (specifically for the button click event).

Private Sub CommandButton_run_Click()
    ' Record history before closing the UserForm
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    Dim lastRow As Long
    ' Find the next available row based on the last used row in Column S
    lastRow = ws.Cells(ws.Rows.Count, "S").End(xlUp).Row + 1

    ' Record input values and the current timestamp
    ws.Cells(lastRow, "S").Value = TextBox_1.Text
    ws.Cells(lastRow, "T").Value = TextBox_2.Text
    ws.Cells(lastRow, "U").Value = Now

    ' Close the UserForm
    Unload Me
End Sub

Code Explanation

1. Determining the Recording Position

lastRow = ws.Cells(ws.Rows.Count, "S").End(xlUp).Row + 1

This line identifies the last entered row in Column S and adds 1 to it. This ensures that new data is always added to the next empty row, creating a history list without overwriting previous entries.

2. Recording the Timestamp

ws.Cells(lastRow, "U").Value = Now

The Now function returns the current date and time from the system. This allows you to track exactly when the data was entered.

3. Closing the Form

Unload Me

This command unloads the UserForm from memory, effectively closing it after the processing is complete.

Application Tips

• User Identification: Adding a field for “User Name” or “Operator” will increase the reliability of the log.
• Validation: You can add an If statement before the recording block to check if the TextBoxes are empty, preventing blank entries.

Summary

Logging UserForm inputs as a history record in VBA is simple to implement.

By automatically saving the date and time, this feature can be used for task logs, reporting histories, and audit trails. This simple mechanism visualizes work progress and streamlines verification tasks.