Spazio 17

Somehow, timeless sayings always have a way of capturing our attention and resurfacing in our thoughts at the most unexpected times.
A while back, I put together a practical guide on how to quickly insert a date into an Excel cell with a simple double-click. WARNING!! By “quickly,” I mean typing the date directly. When this task becomes repetitive, it’s worthwhile to explore ways to automate and streamline the process. These small but valuable functionalities often emerge when working on a file that will accompany us throughout a significant part of our work. Then comes the realization – as the saying goes, “appetite comes with eating” – that the date alone isn’t always enough, and it would also be helpful to include the time. Since this is a space where I share my experiences, here I am explaining how to achieve this.
Follow the steps below.

1. Open the VBA editor:
   • Press Alt + F11 on your Keyboard.
2. Insert a module:
   • In the VBA editor menu, go to “Insert” > “Module”.
3. Paste the next VBA code:
   • copy and past the code in your VBA window

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        Target.Value = Now
        Target.NumberFormat = "dd/mm/yyyy hh:mm:ss" 'Formato data e ora
        Cancel = True 'Impedisce l'azione predefinita del doppio clic
    End Sub

Code Explanation

• Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)this line starts a subroutine that runs before a double-click on a worksheet.
• Target.Value = Now:  this line inserts the current date and time into the cell that was double-clicked.
• Target.NumberFormat = "dd/mm/yyyy hh:mm:ss":  this line sets the cell format to display the date and time in the day/month/year hour:minutes:seconds format. You can adjust this format according to your preferences.
• Cancel = This line prevents the default action of double-clicking, such as entering cell edit mode.

How to use the code

1. After pasting the code, close the VBA editor.
2. Return to the Excel worksheet.
3. Double-click on any cell in the worksheet.
4. The current date and time will be inserted into the cell.

Notes

• This code works for the specific worksheet where it has been inserted. If you want it to work for all worksheets, you will need to place the code in the “This Workbook” module and change the event to Workbook_SheetBeforeDoubleClick.
• You can customize the date and time format by editing the string in  Target.NumberFormat.
• It is important to enable macros in Excel files.

At the end of it all, remember, as mentioned in the other guide Excel: automatically insert date in a cell with Double Click, that enabling macros is not enough; you will also need to save the file with the .xlsm extension.
Personally, when I already know I’ll be working with some written code, I prefer to save the file right away and start fresh with a macro-compatible system. This is simply because sometimes a file in an advanced state of development may exhibit characteristics incompatible with macros. And when the file is packed with formulas, references, charts, and more, it becomes really difficult to figure out what’s making the file incompatible with macros.

If you’re wondering how to make the double click work only on a single cell or a range of cells, rest assured that this is the next guide I’ll create.

Grazie del tuo tempo