Refresh text box with date and time

[vavs]

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.

 

[Legacy 98055]

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="filterrogid: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="filterrogid: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]

Tom,

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

 

[Legacy 98055]

Sure. Go ahead.