VBA / Excel - Subroutines stop while I'm typing

5

I'm working on a spreadsheet where the user has to type a given text into a cell at a certain time. I want to put a side countdown timer so it goes by checking how much time is still missing. However, when I start typing in the cell, the stopwatch function stops working (stops counting), and only returns after I hit Enter. I'm new to VBA, maybe it's some configuration, or where I'm running the subroutines, and I do not know yet.

The countdown routines are as follows:

Private Sub Iniciar_crono()
    starttimer
End Sub

Sub starttimer()
    Application.OnTime Now + TimeValue("00:00:01"), "nexttick"
End Sub

Sub nexttick()
    If Plan3.Range("E3") = 0 Then
        Exit Sub
    End If

    Plan3.Range("E3").Value = Plan3.Range("E3").Value - TimeValue("00:00:01")

    If Plan3.Range("E3").Value <= TimeValue("00:00:10") Then
        Plan2.Shapes("TextBox 1").Fill.ForeColor.RGB = RGB(255, 0, 0)
    Else
        Plan2.Shapes("TextBox 1").Fill.ForeColor.RGB = RGB(255, 255, 255)
    End If

    starttimer

End Sub


Private Sub Parar_crono()
    stoptimer
End Sub

Sub stoptimer()
    On Error Resume Next
    Application.OnTime Now + TimeValue("00:00:01"), "nexttick", , False
End Sub

For guidance: - A button to start the counter calls the start_chrome () - A button to stop it calls Stop_chrome () - It's all done today in Module 1

    
asked by anonymous 23.08.2015 / 00:25

1 answer

5

The timer stops counting, because the moment you are typing in a cell, Excel stops any VBA code execution. I do not know a way to modify this behavior, it is part of Excel design and should have a good reason for it.

Anyway, in the way that the problem is exposed, it will not be possible to solve it due to the Excel design, as stated above.

One possible solution is to make the timer out of Excel. Implemented the same in .NET, for example. So you can instantiate the DLL from Excel and make a button (in Excel) to call the start routine and stop the timer.

    
24.08.2015 / 02:03