M
Michael Link
Hi, everyone--
Here is a macro that deposits the date and time into a cell, but which is
supposed to pop up a warning box when the time is after 5:00PM:
Sub NewDateAndTime()
Dim mPrompt As String
Dim mBoxStyle As Long
Dim mTitle As String
Dim mMsg As Variant
mPrompt = "It's after 5:00 PM! Click OK to enter time, but please remember
to enter TOTAL HOURS WORKED TONIGHT in the yellow box at right.
Thanks!"
mBoxStyle = 64
mTitle = "AFTER-HOURS ENTRY"
With ActiveCell
.Value = Now
.NumberFormat = "mm/dd/yy h:mm AM/PM"
If Now Mod 1 > 17 / 24 Then
mMsg = MsgBox(mPrompt, mBoxStyle, mTitle)
End If
End With
End Sub
The puzzle is this: in its current formulation, a warning box NEVER appears,
regardless of the time of day. However, if I reverse the > sign in the
"If...Then" clause to <, a warning box ALWAYS appears, again regardless of
the time of day. (I have tried replacing the 17/24 designation with its
decimal equivalent [about 0.708333], but the same problem occurs.)
I have no idea what's going wrong. Any ideas how to get the warning to
appear only after 5:00 PM? I appreciate your help--this is driving me nuts!
Here is a macro that deposits the date and time into a cell, but which is
supposed to pop up a warning box when the time is after 5:00PM:
Sub NewDateAndTime()
Dim mPrompt As String
Dim mBoxStyle As Long
Dim mTitle As String
Dim mMsg As Variant
mPrompt = "It's after 5:00 PM! Click OK to enter time, but please remember
to enter TOTAL HOURS WORKED TONIGHT in the yellow box at right.
Thanks!"
mBoxStyle = 64
mTitle = "AFTER-HOURS ENTRY"
With ActiveCell
.Value = Now
.NumberFormat = "mm/dd/yy h:mm AM/PM"
If Now Mod 1 > 17 / 24 Then
mMsg = MsgBox(mPrompt, mBoxStyle, mTitle)
End If
End With
End Sub
The puzzle is this: in its current formulation, a warning box NEVER appears,
regardless of the time of day. However, if I reverse the > sign in the
"If...Then" clause to <, a warning box ALWAYS appears, again regardless of
the time of day. (I have tried replacing the 17/24 designation with its
decimal equivalent [about 0.708333], but the same problem occurs.)
I have no idea what's going wrong. Any ideas how to get the warning to
appear only after 5:00 PM? I appreciate your help--this is driving me nuts!