Display data in text box between certain times

[stuartchaffey]

Hi,
I would like to display a certain text in a text box on a userform if it is between certain times. is this possible?

 

[Jaafar Tribak]

This might work for you :

In the example below, the textbox will show the text "hello" if the time is between 4pm and 4:30pm otherwise no text. Change times to suit your needs.

In the UserForm Module:

Option Explicit

Private bMonitoring As Boolean

Private Sub UserForm_Activate()
    Call MonitorTime
End Sub

Private Sub UserForm_Terminate()
    bMonitoring = False
End Sub

Private Function TimeBetween(ByVal t1 As Date, ByVal t2 As Date) As Boolean
    TimeBetween = CBool(t1 <= Time And t2 >= Time)
End Function

Private Sub MonitorTime()
    If Not bMonitoring Then
        bMonitoring = True
        Do While bMonitoring
            If TimeBetween(#4:00:00 PM#, #4:30:00 PM#) Then
                Me.TextBox1 = "hello"
            Else
                Me.TextBox1 = ""
            End If
            DoEvents
        Loop
    End If
End Sub

 

[stuartchaffey]

Thank you, works a dream. If i wanted to put more than 2 different times for the same text box is that possible?. for example in textbox1 if its between 8&9 display online, and if its between 9&10 display offline?

 

[stuartchaffey]

forgot to say if its outside of those times display unavailable.

 

[Jaafar Tribak]

Thank you, works a dream. If i wanted to put more than 2 different times for the same text box is that possible?. for example in textbox1 if its between 8&9 display online, and if its between 9&10 display offline?

Private Sub MonitorTime()
    If Not bMonitoring Then
        bMonitoring = True
        Do While bMonitoring
            If TimeBetween(#8:00:00 AM#, #9:00:00 AM#) Then
                Me.TextBox1 = "Online"
            ElseIf TimeBetween(#9:00:00 AM#, #10:00:00 AM#) Then
                Me.TextBox1 = "Offline"
            Else
                Me.TextBox1 = "Unavailable"
            End If
            DoEvents
        Loop
    End If
End Sub

Note the AM\PM 12 hour clock system.

 

[stuartchaffey]

Works perfect! Thank you