Application.Ontime not working?

Question

I have a nonmodal form and need to show a countdown timer and perform an action when it is zero.

The code below works in Excel for Windows, not in Excel for Mac 2011 or 2016.

Is there a workaround for Excel for Mac?

Form frmNoteTaker button does this:  

Sub CountDownMeTimer() ' cannot be Private, CountDownTimer application.OnTime would not see it, no error raised

   ' Count down the timer by one second

   Debug.Assert mdtEndTimer > 0

   Me.lblTimer.Caption = Format$(mdtEndTimer - Now(), "nn:ss")

   If mdtEndTimer - Now() < 0 Then ' gone over time

      Me.lblTimer.BackColor = vbRed

   Else

      Me.lblTimer.BackColor = vbWhite

   End If

   ' 1 second resolution is the smallest we can have even though it skips odd seconds

   mdtRefreshTimer = Now() + TimeSerial(0, 0, 1)

   Application.OnTime mdtRefreshTimer, "CountDownTimer", , True 

End Sub

' CountDownTimer must be public in standard module

Public Sub CountDownTimer(Optional ByVal dummy As Long = 0) ' dummy to hide from Macros dialog

frmNoteTaker.CountDownMeTimer

End Sub

Answer 1

I believe that the problem is: there are no non modal forms in Mac VBA.

Answer 2

The timer works when the form is nonmodal on Windows.

On the Mac I've made it modal and the timer works. The user will just have to put up with not being able to access the Excel UI during the process.

Thanks, Bob.

Answer 3

I believe so. That's why your code works on Windows but not on the Mac.

Answer 4

OK.... so if the form was modal the .OnTime would work?