[Excel VBA] How to Automatically Run Code When Entering Text in a Cell (Worksheet_Change)

Introduction

When automating with Excel VBA, there is often a need to “automatically execute a process when a value is entered into a cell.”

In my case, I wanted to automatically enter a date when text was entered in column B, so I researched and implemented this method.

Event Used: Worksheet_Change

To achieve this, use the Worksheet_Change event. This event is automatically called when a cell is modified in a specific sheet.

Process Flow and Preparation

1. Open the Visual Basic Editor.
2. Click Visual Basic from the Excel Developer tab.
3. Double-click the target “Sheet1” from the Project Explorer on the left.
4. Paste the following code into the displayed editor screen.

Actual Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B1:B100")) Is Nothing Then
        Exit Sub
    Else
        Call EnterDate
    End If
End Sub

Code Explanation

Line 1: Definition of Worksheet_Change Event

Private Sub Worksheet_Change(ByVal Target As Range) This event runs automatically when a cell is changed. The range of the changed cell is passed to Target.

Lines 2-3: Do nothing if out of range

If Intersect(Target, Range("B1:B100")) Is Nothing Then
    Exit Sub

If a cell other than B1 to B100 is changed, the process ends.

Lines 4-5: Execute process if in range

Else
    Call EnterDate

If there is input in any cell from B1 to B100, another procedure named EnterDate is called. Note: You can also write the processing content directly here without calling another procedure.

Lines 6-7: End of IF statement and Event

End If
End Sub

Application Tips

• The procedure to be called (EnterDate) must be created in a separate standard module.
• For example, using Target.Offset(0, 1).Value = Date allows you to automatically enter the date next to the input cell.
• You can customize the script by changing the target range to something other than B1:B100.

Summary

By using Excel VBA’s Worksheet_Change event, you can trigger arbitrary processing based on cell input.

This helps improve work efficiency by automating manual tasks such as entering dates or recording logs.