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

mcomp72

Board Regular
Joined
Aug 14, 2016
Messages
165
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.
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

mcomp72

Board Regular
Joined
Aug 14, 2016
Messages
165
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,206
Messages
5,600,313
Members
414,375
Latest member
Onmyown

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