VBA/Excel - Subroutines stop while typing

Asked

Viewed 462 times

6

I am 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 countdown side, so he can check how much time is left. However, when I start typing in the cell, the stopwatch function stops working (stops counting), and only comes back after I give Enter. I’m new in VBA, maybe it’s some configuration, or where I’m running the subroutines, and I don’t know yet.

The regressive counter routines are these:

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 on: - A button to start the counter calls Start_chrono() - A button to stop it calls Parar_crono() - Everything is now run in Module 1

excel vba

  • As the colleague @Cantoni already put well in his reply, Excel blocks the execution of the timer while in edit mode. I went online to see if you could do that with native Windows timer functions. And it is, because unlike the VBA, they are not blocked. Only, even so, you will incur another problem: Excel will probably crash if you try to change a cell value while it is in edit mode. :/

  • Okay, I get what I’m doing wrong. I’ll try other means. An option I thought is to fill a Floating Text Box instead of a cell. I did a test, and it does not stop the execution of VBA code. I didn’t want to do much outside Excel, because I’m making this application mainly to learn VBA, so the bigger the challenge the better! =)

  • If it’s just to learn, then you’ve already learned something. ;) But, an alternative to do what you want is to use a text field in a user form (more information here: http://www.macoratti.net/12/vba_lgn1.htm). Thus, you have full control over what the user can type, or how long it will take for that. And, you can transfer the data from there to the spreadsheet cells (which may be blocked for editing).

1 answer

5


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

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

A possible solution is to do the timer outside of Excel. Implemented the same in . NET, by exmeplo. Thus, you can instantiate the DLL from Excel and make a button (in Excel) to call the routine to start and stop the timer.

  • You are right about Excel temporarily locking the timer while in edit mode. But I don’t think the AP code is wrong. He’s just re-scheduling the timer call for a second forward again. There is no stacking of calls there, and so there will be stackoverflow. :)

  • 1

    It is true @Luizvieira, the code the way it is has led me to a misinterpretation. I will withdraw that part of the answer. Thank you!

Browser other questions tagged excel vba

You are not signed in. Login or sign up in order to post.