CLose a Userfom After 10 Seconds or When User Clicks Button

[sxhall]

Hi,

Have been puzzling this for some time and cannot figure out where to put or structure the code.

I have a 'Welcome' userform that displays for an employee when they enter there employee number in to another userform. What I need is both of the below to be available to close the 'Welcome' userform...

1. Automatically close the 'Welcome' userform after 10 seconds and revert back to the initial screen
2. User can click a command button to close the 'Welcome' userform

Need both options as if they don't click on the button to close the 'Welcome' userform HR require that it is not still shown continually until the close button is clicked.

What I cannot overcome with my current code are the below...

• Code below errors and does not close the userorm after 10 seconds, the timer only starts once I have closed the 'Welcome' userform and the it errors as the 'Welcome' form is already closed!
• I have to click on the Close button on the 'Welcome' userform to initate the timer to automatically close the 'Welcome' userform!

Any assistance will be greatly appreciated.


Initial From & Code...

Private Sub TextBox1_Change()

Dim FoundCell As Range
Dim EmpID As String

Set ws = Sheet3

If Me.TextBox1 = "" Then Exit Sub

EmpID = TextBox1.Value * 1

Set FoundCell = ws.Range("D:D").Find(EmpID, LookIn:=xlValues, LookAt:=xlWhole)

If Not FoundCell Is Nothing Then

Welcome.Show

Application.Wait (Now + TimeValue("0:00:10"))     '*** Always waits here after I have clicked close on the 'Welcome'  userform

Welcome.Hide        '*** currently errors here when I click close on the welcome screen as it is already closed!

With Me
    .TextBox1.Value = ""
    .TextBox1.SetFocus
End With

Else

End If

End Sub

Welcome Screen & Code...

Private Sub CommandButton1_Click()

Me.Hide

End Sub

 

[Jaafar Tribak]

If I understood you correctly, you just need to autoclose the Welcome form after 10 secs of inactivity or when the Close button is closed.

Try this and see how it goes:

1- Initial Form code:

Option Explicit

Private Sub TextBox1_Change()

    Dim FoundCell As Range
    Dim EmpID As String
    Dim ws As Worksheet

    Set ws = Sheet3
    If Me.TextBox1 = "" Then Exit Sub
    EmpID = TextBox1.Value * 1
    Set FoundCell = ws.Range("D:D").Find(EmpID, LookIn:=xlValues, LookAt:=xlWhole)
    If Not FoundCell Is Nothing Then
        Welcome.Wait = 10!
        Welcome.Show
        With Me
            .TextBox1.Value = ""
            .TextBox1.SetFocus
        End With
    Else

    End If

End Sub

2- Welcome Form code:

Option Explicit

Private sngWait As Single

Public Property Let Wait(ByVal vNewValue As Single)
    sngWait = vNewValue
End Property

Private Sub UserForm_Activate()
    Static sngStart As Single
    If sngStart Then Exit Sub
    sngStart = Timer: Do: DoEvents: Loop Until Timer - sngStart >= sngWait: sngStart = 0!
    Unload Me
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = 0 Then CommandButton1.Value = True
End Sub

Private Sub CommandButton1_Click()
    sngWait = 0!
End Sub

EDIT:
I don't think using the Textbox change event is appropriate here ... Try placing the Initial form code using some other TextBox Event such as the AfrerUpdate or Exit events, or simply add a button to display the Welcome form.

 

[sxhall]

If I understood you correctly, you just need to autoclose the Welcome form after 10 secs of inactivity or when the Close button is closed.

Try this and see how it goes:

1- Initial Form code:

Option Explicit

Private Sub TextBox1_Change()

    Dim FoundCell As Range
    Dim EmpID As String
    Dim ws As Worksheet

    Set ws = Sheet3
    If Me.TextBox1 = "" Then Exit Sub
    EmpID = TextBox1.Value * 1
    Set FoundCell = ws.Range("D:D").Find(EmpID, LookIn:=xlValues, LookAt:=xlWhole)
    If Not FoundCell Is Nothing Then
        Welcome.Wait = 10!
        Welcome.Show
        With Me
            .TextBox1.Value = ""
            .TextBox1.SetFocus
        End With
    Else

    End If

End Sub

2- Welcome Form code:

Option Explicit

Private sngWait As Single

Public Property Let Wait(ByVal vNewValue As Single)
    sngWait = vNewValue
End Property

Private Sub UserForm_Activate()
    Static sngStart As Single
    If sngStart Then Exit Sub
    sngStart = Timer: Do: DoEvents: Loop Until Timer - sngStart >= sngWait: sngStart = 0!
    Unload Me
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = 0 Then CommandButton1.Value = True
End Sub

Private Sub CommandButton1_Click()
    sngWait = 0!
End Sub

EDIT:
I don't think using the Textbox change event is appropriate here ... Try placing the Initial form code using some other TextBox Event such as the AfrerUpdate or Exit events, or simply add a button to display the Welcome form.

Many Thanks Jaafar,

This was exactly what I was looking for

I was triggering the code first with afterupdate but changed it at some point to the change event. Will revert it back to afterupdate

 

[Jaafar Tribak]

Many Thanks Jaafar,

This was exactly what I was looking for

I was triggering the code first with afterupdate but changed it at some point to the change event. Will revert it back to afterupdate

Glad it worked as expected.
I re-read your code again. I think the TextBox change event should be fine after all.