Userform auto close after 2 minutes

Jeorge

New Member
Joined
Nov 27, 2015
Messages
32
Hi Sir/Ma'am,

1. I created a userform that needs information from my office mates like phone number and/or address, I wanted the form to automatically close after a period of time like 2 minutes or less as it happened that after someone opened the form it was left open on one computer which basically restrict others to access it. IF there is a command that I can add to my code to auto close it then that would be very much appreciated.
2. Next question is there a way refresh my time stamp? After I opened my userform time stamp and date stamp the time stamp freeze from the time userform opens, (Please see command below for reference).

Thanks a lot guys for any help

Jeorge


Private Sub Enter_Click()

Dim LastRow As Object

Set NextRow = Sheet1.Range("A10000").End(xlUp)

NextRow.Offset(1, 0).Value = TextBox1.Text
NextRow.Offset(1, 1).Value = ComboBox1.Text
NextRow.Offset(1, 2).Value = TextBox2.Text
NextRow.Offset(1, 3).Value = tbDate.Text
NextRow.Offset(1, 4).Value = tbtime.Text

MsgBox "Phone number Added"

response = MsgBox("Do you want to enter another Phone number?", _
vbYesNo)

If response = vbYes Then
TextBox1.Value = ""
ComboBox1.Value = ""
TextBox2.Value = ""
'This is the missing command to either refresh the time for the next data encoded

TextBox1.SetFocus
Else
Unload Me
End If

End Sub
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
You can do it with an OnTime event in the form.

Create a module and put this code in. Use the name for your userform in place of UserForm1
VBA Code:
Sub AutoCloseForm()
Unload UserForm1
End Sub

In your userform code area put in the following code

VBA Code:
Private Sub UserForm_initialize()
Dim TimeStamp
Dim TaDval
Dim DelayTime

TimeStamp=Now
DelayTime = "00:02:00" ' Set the delay in here. Try 00:00:10 for a 10 second delay. This value is for 2 minutes
Private Sub UserForm_initialize()
TaDval =TimeStamp + TimeValue(DelayTime)
Application.OnTime TaDval, "AutoCloseForm"
End Sub

You also have a TimeStamp from when the form was opened, which you can use.
 
Upvote 0
Wow! It did solved my issue on that! Thank you very much Count Tepes, how about my 2nd question if you may have any answer on that :biggrin:
 
Upvote 0
I am not quite sure what you want to do.

Are you trying to timestamp entries as they are made, or timestamp something when you open the document?
 
Upvote 0
I am not quite sure what you want to do.

Are you trying to timestamp entries as they are made, or timestamp something when you open the document?
Hi CountTepes

I already got the Time stamp but the time stay on the time as when it was opened, Is it possible that the time stamp also changes if clicked vbYes? Because my userform stay open if vbYes so the time stamp doesn't change, hope there's a code to refresh the time stamp if I click YES

MsgBox "Phone number Added"

response = MsgBox("Do you want to enter another Phone number?", _
vbYesNo)

If response = vbYes Then
TextBox1.Value = ""
ComboBox1.Value = ""
TextBox2.Value = ""

Thank you very much
 
Upvote 0
Just add TimeStamp = Now(), into the If response block

If your TimeStamp variable is in a different subroutine, then declare it outside of any routine, to make it global. It will then keep the value independently of where it was set.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

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
Back
Top