VBA Timer Using Do Loop

Question

I am trying to make a countdown in VBA, that displays the seconds in a textbox1. I am using this code below, but nothing happens and the textbox doesn't increment each second. I have tried doing Endtick / 1000 too because it's ms, but to still now avail. Is this the right method I should be using?

Other stuff is happening in the app as the timer is running, so I can't use the WAIT function.

Private Sub CommandButton2_Click()
    timer (10)
End Sub




Sub timer(Finish As Long)
 
    Dim NowTick As Long
    Dim EndTick As Long
 
    EndTick = GetTickCount + (Finish * 1000)
     
    Do
 
        NowTick = GetTickCount
        DoEvents
        UserForm1.TextBox1.Text = GetTickCount
        
 
    Loop Until NowTick >= EndTick
    msgbox("Time is up")
 
End Sub

It works for me -- IF you put the timer sub in a module and not in with the user form. What is shown in the textbox is not counting down, however. It shows the Long count of milliseconds from GetTickCount. So you'll have to capture the tick count at the start, then make it decrement as you go along. — PeterT
– PeterT, Commented Jan 12, 2021 at 21:20
Hi, I tried it in a module, howevever no message is popping up at the end... nothing seems to show in my textbox at all, although it looks like its trying as I cant type in it — BCLtd
– BCLtd, Commented Jan 12, 2021 at 21:23

PeterT · Accepted Answer · 2021-01-12 21:33:25Z

1

Add this to a module, separate from the userform code:

Option Explicit

#If Win64 Then
    Public Declare PtrSafe Function GetTickCount Lib "kernel32" () As Long
#Else
    Public Declare Function GetTickCount Lib "kernel32" () As Long
#End If

Sub timer(Finish As Long)
    Dim EndTick As Long
    EndTick = GetTickCount + (Finish * 1000)
    Do
        DoEvents
        UserForm1.TextBox1.Text = (EndTick - GetTickCount) / 1000
    Loop Until GetTickCount >= EndTick
    UserForm1.TextBox1.Text = 0
    MsgBox ("Time is up")
End Sub

answered Jan 12, 2021 at 21:33

PeterT

8,5971 gold badge19 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BCLtd Over a year ago

Hi Peter, that did exactly what I wanted - above and beyond. I was missing the libs . thank you for taking the time, it is very much appreciated.

Collectives™ on Stack Overflow

VBA Timer Using Do Loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related