How to activate a cell on Double Click on VBA(Excel)? Please assist me fix the code

Question

I am designing a simple stock clock. The stopwatch starts once I click a cell. I want to do other stuffs in another cell while the clock is running. I have the start button shape as Macro(start function assigned) and the stop button shape as Macro(stop function assigned).

When I click another cell, it becomes active and the clock runs from there. If I click the stop button before moving on the next cell, the clock stops completely but I am losing track of time. Please assist me fix the code.

I want to activate the cell by double clicking so that while the clock runs, I am able to do other stuffs in another cell.

Note: I do not want to manually specify the cell like Range("A1") each time, rather I want to double click on the cell to start the stopwatch or call the timer function.

Below is my code:

Dim Tick As Date, t As Date
Sub stopwatch()

t = Time
Call StartTimer

End Sub

Sub StartTimer()
Tick = Time + TimeValue("00:00:01")

ActiveCell.Value = Format(Tick - t - TimeValue("00:00:01"), "hh:mm:ss")
Application.OnTime Tick, "StartTimer"
End Sub

Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=Tick, Procedure:="StartTimer", Schedule:=False
End Sub

Sub InsertCurrentTime()
ActiveCell.Value = Time
ActiveCell.NumberFormat = "h:mm:ss AM/PM"
End Sub

You always write to the ActiveCell. You have to change that and write to a fixed cell like A1. — Storax, Sep 28 '22 at 10:08

Storax · Accepted Answer · 2022-09-28T10:42:52.180

I do not completely understand what the OP is after but the following code will write to cell A1 and you can work in other cells as well. But you have to be aware that the code will not write to cell A1 as long as you are in Edit mode within Excel. But it will write the correct time to A1 as soon as you leave Edit mode.

Option Explicit

Dim Tick As Date, t As Date
Dim myCell As Range

Sub stopwatch()

    t = Time
    Call StartTimer

End Sub

Sub StartTimer()
    Tick = Time + TimeValue("00:00:01")
    Set myCell = Range("A1")
    myCell.Value = Format(Tick - t - TimeValue("00:00:01"), "hh:mm:ss")
    Application.OnTime Tick, "StartTimer"
End Sub

Sub StopTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=Tick, Procedure:="StartTimer", Schedule:=False
End Sub

Sub InsertCurrentTime()
    myCell.Value = Time
    myCell.NumberFormat = "h:mm:ss AM/PM"
End Sub

Update Based on the comment the OP might be after that

Option Explicit

Dim Tick As Date, t As Date
Global myCell  As Range

Sub stopwatch()

    t = Time
    Call StartTimer

End Sub

Sub StartTimer()
    Tick = Time + TimeValue("00:00:01")
    myCell.Value = Format(Tick - t - TimeValue("00:00:01"), "hh:mm:ss")
    Application.OnTime Tick, "StartTimer"
End Sub

Sub StopTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=Tick, Procedure:="StartTimer", Schedule:=False
End Sub

Sub InsertCurrentTime()
    myCell.Value = Time
    myCell.NumberFormat = "h:mm:ss AM/PM"
End Sub

In the worksheet you have to add

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Set myCell = Target
    StartTimer
    Cancel = True
End Sub

A double click in a cell will start the watch. But the code as it is will not really keep track of the different timers.

I knew this already. But I don't want to edit the VBA code every time manully. My issue was , I want to double click the cell , and based on number of clicks (preferably double click), the cell should become active- so the work might become easier. — Habeeb E Sadeed, Sep 28 '22 at 10:19
I updated the answer. BTW, yiou can edit your post by clicking on the edit button and add any neccessary information there as well. That makes it easier for future readers to follow. — Storax, Sep 28 '22 at 10:43
Thanks and great job! It is working. I already edited the post. — Habeeb E Sadeed, Sep 28 '22 at 10:58

How to activate a cell on Double Click on VBA(Excel)? Please assist me fix the code

1 Answers1