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

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

CodePudding user response：

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.