Refresh text box with date and time

vavs

Well-known Member
Joined
Jul 2, 2004
Messages
514
I have a VBA form with the date and time displayed. It will refresh each time a user enters a value in the combo box on the form. I would like to have it refresh every minute or two so that the user will see a relatively accurate date and time.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
L

Legacy 98055

Guest
I'll walk you through creating a simple example and provide a download that is the result of the instructions given. Between the two, you should not have any problems adapting this method to your current userform.

Open a new workbook, add a userform("UserForm1"), add a Label and change its name to "DateAndTime". Add a standard module. Paste the first code listing into the userform, the second into the standard module.

Download the example if you need to.

UpdateDateAndTimeInUserform.zip

<table width="100%" border="1" bgcolor="White" style="filter:progid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New>  <font color="#0000A0">Option</font> <font color="#0000A0">Explicit</font>

  <font color="#0000A0">Private</font> RunWhen <font color="#0000A0">As</font> <font color="#0000A0">Date</font>
  <font color="#008000">'in seconds</font>
  <font color="#0000A0">Private</font> <font color="#0000A0">Const</font> UpdateInterval <font color="#0000A0">As</font> <font color="#0000A0">Double</font> = 1

  <font color="#0000A0">Friend</font> <font color="#0000A0">Sub</font> UpDate()
       DateAndTime.Caption = Format(Now, "General Date")
       RunWhen = Now + TimeSerial(0, 0, UpdateInterval)
       Application.OnTime RunWhen, "NotifyMe", , <font color="#0000A0">True</font>
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

  <font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> UserForm_Initialize()
      <font color="#008000"> 'other code</font>
       <font color="#0000A0">Call</font> UpDate
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

  <font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> UserForm_Terminate()
      <font color="#008000"> 'other code</font>
       <font color="#0000A0">On</font> <font color="#0000A0">Error</font> <font color="#0000A0">Resume</font> <font color="#0000A0">Next</font>
       Application.OnTime RunWhen, "NotifyMe", , <font color="#0000A0">False</font>
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table><button onclick='document.all("917200613521384").value=document.all("917200613521384").value.replace(/<br \/>\s\s/g,"");document.all("917200613521384").value=document.all("917200613521384").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("917200613521384").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="917200613521384" wrap="virtual">
Option Explicit

Private RunWhen As Date
'in seconds
Private Const UpdateInterval As Double = 1

Friend Sub UpDate()
DateAndTime.Caption = Format(Now, "General Date")
RunWhen = Now + TimeSerial(0, 0, UpdateInterval)
Application.OnTime RunWhen, "NotifyMe", , True
End Sub

Private Sub UserForm_Initialize()
'other code
Call UpDate
End Sub

Private Sub UserForm_Terminate()
'other code
On Error Resume Next
Application.OnTime RunWhen, "NotifyMe", , False
End Sub</textarea>

<table width="100%" border="1" bgcolor="White" style="filter:progid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New>  <font color="#0000A0">Option</font> <font color="#0000A0">Explicit</font>

  <font color="#0000A0">Sub</font> NotifyMe()
       <font color="#0000A0">Call</font> UserForm1.UpDate
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table><button onclick='document.all("917200613534727").value=document.all("917200613534727").value.replace(/<br \/>\s\s/g,"");document.all("917200613534727").value=document.all("917200613534727").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("917200613534727").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="917200613534727" wrap="virtual">
Option Explicit

Sub NotifyMe()
Call UserForm1.UpDate
End Sub</textarea>

UpdateDateAndTimeInUserform.zip
 

vavs

Well-known Member
Joined
Jul 2, 2004
Messages
514
Tom,

That works famously. Thanks for the help. Since I learned something new today, can I take the rest of the day off?
 

Forum statistics

Threads
1,136,994
Messages
5,679,030
Members
419,800
Latest member
spvsr999

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