Need help making a UserForm stay on screen for fixed amount of time

[mcomp72]

I have a macro-enabled workbook where I have a UserForm appear when the workbook is first opened. While it is on the screen, so code runs in the background. It used to be that I didn't care how long this UserForm was on screen for. It could be 1 second, it could be 5 seconds, but didn't really matter. Now I would like to make it so it stays on the screen for at least 4 seconds. I found some code online that can track time: two subs, actually, StartTimer and EndTimer. I also found some code that would make Excel delay, which is called SleepSub. They have worked for me in other instances, but in this case, the UserForm does not stay on the screen but maybe 1 second.

Here's the code I'm using.

Private Sub UserForm_Activate()

Dim NeededTime As Double

Call StartTimer

Call General_Subs.RemainderOfWorkbookOpen

Call EndTimer

NeededTime = 4 - SecondsElapsed

Debug.Print "Needed Time = " & NeededTime

If NeededTime > 0 Then Call SleepSub(NeededTime)

Unload Me

End Sub

Sub StartTimer()

StartTime = Timer

End Sub

Sub EndTimer()

SecondsElapsed = Round(Timer - StartTime, 2)

End Sub

Sub SleepSub(vSeconds As Variant)
'this sub will delay the code running for however many seconds are in vSeconds.

Dim t0 As Double, t1 As Double

On Error Resume Next

t0 = Timer

Do
    t1 = Timer
    If t1 < t0 Then t1 = t1 + 86400 'Timer overflows at midnight
    DoEvents    'optional, to avoid excel freeze while sleeping
Loop Until t1 - t0 >= vSeconds

On Error GoTo 0

End Sub

FYI - SecondsElapsed and StartTime are global variables, since I use them in other modules.

After I open the workbook, I look in my Intermediate Window, and it tells me NeededTime is about 1.5 seconds. That seems to be all of the time that the UserForm is on the screen for. I would have through this code would make it display for 1.5 MORE seconds than it already has, rather than ONLY 1.5 seconds.

Does anyone have any idea why the UserForm would not be staying on the screen for at least 4 seconds?

Edit: I should point out that I need this workbook to run on both Windows & Mac, Excel 2011 for Mac in particular. It doesn't support modeless UserForms, so I can't use those.

Edit: I should also point out that the three subs (StartTimer, EndTimer, and SleepSub) are not in the UserForm module. They are in a standard module. I call those at other times, so it seemed best to put them in a separate module.

 

[mcomp72]

I just solved my problem. I decided to try calling the SleepSub for 1 second right after the StartTimer line, and that made it work. Not sure why it worked, but I'll take it.

In case it is helpful to anyone else, here's the new code, with the line I added.

Private Sub UserForm_Activate()

Dim NeededTime As Double

Call StartTimer

Call SleepSub(1) ' <- THIS IS WHAT I ADDED

Call General_Subs.RemainderOfWorkbookOpen

Call EndTimer

NeededTime = 4 - SecondsElapsed

Debug.Print "Needed Time = " & NeededTime

If NeededTime > 0 Then Call SleepSub(NeededTime)

Unload Me

End Sub

 

[Legacy 456155]

I see you solved your problem. Nevertheless, I took the time to reply. Maybe this will help someone else. I don't have a MAC, but am assuming that OnTime works for MACS.

In userform:

Private Sub UserForm_Initialize()
    StartCountDown Me, 5
End Sub

Public Sub OnTimeCallback()
    Unload Me
End Sub

In standard module:

Private o As Object

Public Sub StartCountDown(ForThisObject As Object, ForThisManySeconds As Integer)
    Set o = ForThisObject
    Application.OnTime TimeSerial(0, 0, ForThisManySeconds), "StopCountDown"
End Sub

Public Sub StopCountDown()
    o.OnTimeCallback
    Set o = Nothing
End Sub

See "AutoClosingUserForm.xlsm" in this folder.