Using If Statements and a Time Variable

[Rgreenspan]

I am trying to run a macro upon opening a Workbook that will display a
short message based on the time of day.
If the current time is less than the time i.e. in mytime then it will
dipslay a "Good Morning Message in a box." Which is accomplished
when it goes to the Good Morning code.

My problem is that i cannot the the VBA to work and display (Good
Morning, Good Afternoon, or Good Night).

I cannot get it to work for all time variables. It seems that the
time is only read by its value such as if the current time its 11:15
PM my first If statement sees it as true. How do i get it to read as
11:15 PM or 23:15:00 (military time)?

Any help?



mytime1 = TimeValue("11:59:00")
mytime2 = TimeValue("20:59:00")
mytime3 = TimeValue("23:59:00")
Sheets("Estimate").Select

If mytime1 > Range("mytime") Then
GoTo Good_Morning
Else
If mytime2 > Range("mytime") Then
GoTo Good_AFTERNOON
Else
If mytime3 > Range("mytime") Then
GoTo Good_Evening
End if

RG

 

[Guest]

Hi,

Try something like this:-

Sub Time_Test()

Dim myTime As Date 'Note times as actually fractions of a date
Dim myMsge As String

myTime = Now - Date

Select Case myTime
Case TimeValue("00:00:01") To TimeValue("11:59:59")
myMsge = "Good Morning"
Case TimeValue("12:00:00") To TimeValue("18:59:59")
myMsge = "Good Afternoon"
Case TimeValue("12:00:01") To TimeValue("23:59:59")
myMsge = "Good Evening"
End Select

MsgBox myMsge

End Sub


Regards,

OssieMac

 

[Guest]

My apologies. The third case statement had the wrong starting time. Should
have been:-

Case TimeValue("18:00:00") To TimeValue("23:59:59")
myMsge = "Good Evening"
Regards,

OssieMac

 

[Guest]

Hopefully third time lucky. I had the time of second case statement
overlapping the third case. Anyway Ihave now included a line where you can
enter dummy values for the time to test it and when finished testing, delete
the line.

Sub Time_Test()
Dim myTime As Date 'Note time is actually a fraction of a date
Dim myMsge As String

myTime = Now - Date 'Now returns date and time. Date returns date only.

'Can uncomment and use following line for testing then delete it
'myTime = TimeValue("23:59:59")

Select Case myTime
Case TimeValue("00:00:00") To TimeValue("11:59:59")
myMsge = "Good Morning"
Case TimeValue("12:00:00") To TimeValue("17:59:59")
myMsge = "Good Afternoon"
Case TimeValue("18:00:00") To TimeValue("23:59:59")
myMsge = "Good Evening"
End Select

MsgBox myMsge

End Sub

regards,

OssieMac

 

[JE McGimpsey]

Another way:

Public Sub Time_Test2()
Select Case Time
Case Is < #12:00:00 PM#
MsgBox "Good Morning"
Case Is < #6:00:00 PM#
MsgBox "Good Afternooon"
Case Else
MsgBox "Good Evening"
End Select
End Sub

 

[RichG]

Hopefully third time lucky. I had the time of second case statement
overlapping the third case. Anyway Ihave now included a line where you can
enter dummy values for the time to test it and when finished testing, delete
the line.

Sub Time_Test()
Dim myTime As Date 'Note time is actually a fraction of a date
Dim myMsge As String

myTime = Now - Date 'Now returns date and time. Date returns date only.

'Can uncomment and use following line for testing then delete it
'myTime = TimeValue("23:59:59")

Select Case myTime
Case TimeValue("00:00:00") To TimeValue("11:59:59")
myMsge = "Good Morning"
Case TimeValue("12:00:00") To TimeValue("17:59:59")
myMsge = "Good Afternoon"
Case TimeValue("18:00:00") To TimeValue("23:59:59")
myMsge = "Good Evening"
End Select

MsgBox myMsge

End Sub

regards,

OssieMac

Hi OssieMac

Thanks so much.
That worked great.
I was not familiar with the Select Case Statement.
I Appreciate your help.

sincerely,
RichG

 

[RichG]

Another way:

Public Sub Time_Test2()
Select Case Time
Case Is < #12:00:00 PM#
MsgBox "Good Morning"
Case Is < #6:00:00 PM#
MsgBox "Good Afternooon"
Case Else
MsgBox "Good Evening"
End Select
End Sub









- Show quoted text -

That works too. Thanks alot for your help. Again, I was not aware of
the "Select Case" statement, but will definitely use it from now on.

Sincerely,
RichG