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

mcomp72

Board Regular
Joined
Aug 14, 2016
Messages
218
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2011
Platform
  1. Windows
  2. MacOS
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.

VBA Code:
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.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

mcomp72

Board Regular
Joined
Aug 14, 2016
Messages
218
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2011
Platform
  1. Windows
  2. MacOS
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.

VBA Code:
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
 
L

Legacy 456155

Guest
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:
VBA Code:
Private Sub UserForm_Initialize()
    StartCountDown Me, 5
End Sub

Public Sub OnTimeCallback()
    Unload Me
End Sub

In standard module:
VBA Code:
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,123
Messages
5,768,246
Members
425,460
Latest member
Astros1243

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top