Data Validation & Macros

M

Michael Link

Geez--this is my second post of the morning. Sorry about not thinking my
queries through all the way!

Anyway, I have the following macro which deposits the date and time into a
cell:

Sub DateAndTime()
With ActiveCell
.Value = Now
.NumberFormat = "mm/dd/yy h:mm AM/PM"
End With
End Sub

Unfortunately, the stamp deposited into the cell seems not to be recognized
by Data Validation, which is set up to flash a warning box if the time entered
is after 5:00 PM. If I enter the identical info that the macro generates
manually and hit "Enter," THEN Validation pops the warning box up.

What am I doing wrong? Can I rewrite the macro so that it essentially hits
"Enter" at the end so that validation will work?

Thanks!
 
J

Jason Morin

You can just pop up a msgbox as an alternative:

Sub DateAndTime()
Dim mPrompt As String
Dim mBoxStyle As Long
Dim mTitle As String
Dim mMsg As Variant

mPrompt = "It's past 5:00 PM!"
mBoxStyle = 16 ' vbCritical
mTitle = "Warning!"

With ActiveCell

.Value = Now
.NumberFormat = "mm/dd/yy h:mm AM/PM"

If Now Mod 1 > 17 / 24 Then ' 17/24 = 5:00 PM
mMsg = MsgBox(mPrompt, mBoxStyle, mTitle)
.ClearContents
End If

End With

End Sub
 
M

Michael Link

Thanks, Jason! I'll give it a shot. My experience with macros is pretty limited,
so I would never have come up with this. Thanks again!
 
Top