[SOLVED] Create Splash Screen in Excel with a variable timer and variable text

Create Splash Screen in Excel with a variable timer and variable text

I have a Userform. On the Userform is a Label.

In the Userform_Initialize() event I use the code:

to close the Userform after x seconds have elapsed.

In a module I have the

sub KillForm()
Unload UserForm1
end sub

Now I want to open the Userform through a button-click on a worksheets, and pass the string strMessage for the label and the seconds x to the userform, so I create a sub in a module

Sub ShowMessage(strMessage As String, x As Integer)
UserForm1.Label1.Caption = strMessage
userform1.show
End Sub

and call the routine at the On_Click event of the button

ShowMessage "Hello World.", 3

Allas, it doesn't work. x is not 3 on the userform, always 0.

I tried Public x as Integer on top of the Userform module and the general module. No luck. When I replace x with a number such as 3 in the OnTime line it works, the form appears with strMessage as label caption, and it disappears after the static time. But I would like to make the time dynamic with the variable x.

So: How can I pass the value of x to the Userform and into the routine

Application.OnTime Now + TimeSerial(0, 0, x), "KillForm"

Thanks AG

Solution

Assuming that no other operation is required to run in the background during the display of the splashscreen, you could also modify ShowMessage the following way:

Sub ShowMessage(strMessage As String, intDisplayTime As Integer)
    Dim dblStartTime as Double

    UserForm1.Label1.Caption = strMessage
    UserForm1.Show

    dblStartTime = Timer
    If dblStartTime + intDisplayTime >= 86400 Then 
        dblStartTime = dblStartTime - 86400
    End If

    While Timer - dblStartTime < intDisplayTime
        DoEvents
    Wend

    Unload UserForm1    
End Sub