VBA & Macro Problems

How to Create an Excel Timestamp When a Cell is Double-Clicked

Maira Mehtab Oct 13, 2026 871 views

Question details

The user wants to automatically insert a static current date and time in a specific column when a cell in another column is double-clicked, avoiding the volatile recalculations of the NOW() formula.

How to Create an Excel Timestamp When a Cell is Double-Clicked

Product: Excel
Device & OS: not provided
Scenario: Tracking timestamps for data entry where entering data triggers a permanent time record that does not update upon spreadsheet recalculation.
Observed behavior: The goal is to successfully trigger a VBA Worksheet_BeforeDoubleClick event to insert a static Now value into a target cell.

Before you start

Ensure your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) and that macros are enabled in your Trust Center settings so the VBA code can execute properly.

Solution 1Recommended

Use Worksheet_BeforeDoubleClick VBA Macro (Offset Timestamp)

This method allows you to double-click a cell in one column (e.g., Column C) and automatically insert a timestamp into an adjacent column (e.g., Column A).

By utilizing the Worksheet_BeforeDoubleClick event, you can run an automated script exactly when you interact with specific cells. Using an offset ensures the timestamp is placed safely next to your active work area without overwriting it.

Open the VBA Editor

Right-click the worksheet tab at the bottom of your Excel window and select 'View Code' to open the Visual Basic for Applications (VBA) editor.

Paste the Event Macro

In the code window that appears, paste the following script: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Not Intersect(Range("C2:C50"), Target) Is Nothing Then Target.Offset(0, -2).Value = Now Cancel = True End If End Sub

Customize and Save

Adjust the Range("C2:C50") to fit your data range. The Offset(0, -2) instruction moves the timestamp 0 rows down and 2 columns to the left (Column A). Close the VBA editor and save your file as a Macro-Enabled Workbook (.xlsm).

Use Worksheet_BeforeDoubleClick VBA Macro (Offset Timestamp)

Preventing Edit Mode: The 'Cancel = True' line in the macro prevents Excel from entering its default cell edit mode when you double-click, making the timestamping process smooth.

Solution 2

Insert Timestamp Directly into the Double-Clicked Cell

If you prefer the timestamp to appear exactly in the cell you are double-clicking, you can modify the target range and remove the offset command.

Efficient Spreadsheet Management

Use WPS Spreadsheet to Run Macros and Track Data

WPS Office offers robust support for advanced spreadsheet functionalities, including VBA macros in its advanced versions. You can seamlessly apply this double-click timestamp script using WPS Spreadsheet for effective data tracking.

1. Enable the Developer Tab: Open your spreadsheet in WPS Office, go to the top ribbon, and ensure the Developer tab is active. If your version supports VBA, you will see the 'Visual Basic' button.
2. Open the VBA Editor: Click on 'Visual Basic' in the Developer tab. In the Project Explorer pane on the left, double-click the specific Sheet where you want the timestamp.
3. Insert the Macro Code: Paste the Worksheet_BeforeDoubleClick macro into the code window. Ensure your target ranges and offsets match your spreadsheet layout.
4. Save as Macro-Enabled: Close the Visual Basic editor and go to Menu > Save As. Choose 'Excel Macro-Enabled Workbook (*.xlsm)' to ensure your timestamps continue working.

100% compatible with Microsoft Excel formats, including .xlsm filesFamiliar interface with a built-in Developer tab and VBA supportLightweight application that opens large data files rapidlyFree to download and use for all standard office needs

microsoft office alternative - wps office

Frequently Asked Questions

Why shouldn't I just use the NOW() formula for timestamps?

The NOW() and TODAY() formulas are 'volatile' functions. This means they recalculate and update to the current time every time the spreadsheet is opened or whenever any calculation occurs in the file. A VBA macro inserts the time as a permanent, static text/value that will never change.

How do I format the inserted timestamp to show only the time?

You can format the target column (e.g., Column A) using Excel's built-in Cell Formatting. Select the column, press Ctrl+1 to open Format Cells, go to the Number tab, select 'Custom', and enter a format like 'hh:mm AM/PM'. The macro will still insert the full date/time, but the cell will only display the time.

Can I apply this double-click macro to multiple non-adjacent columns?

Yes. You can modify the Intersect range in the VBA code to include multiple specific ranges. For example, changing the code to 'Range("C2:C50, E2:E50")' will trigger the macro when cells in either column C or E are double-clicked.

Excel & Data Problems