Data Validation & Macros

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=20
cell:

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

Unfortunately, the stamp deposited into the cell seems not to be =
recognized=20
by Data Validation, which is set up to flash a warning box if the time =
entered=20
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=20
"Enter" at the end so that validation will work?

Thanks!

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

---
HTH
Jason
Atlanta, GA

>-----Original Message-----
>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!
>.
>

Thanks, Jason! I'll give it a shot. My experience with macros is pretty =
limited,=20
so I would never have come up with this. Thanks again!
>-----Original Message-----
>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 =3D "It's past 5:00 PM!"
>mBoxStyle =3D 16 ' vbCritical
>mTitle =3D "Warning!"
>
>With ActiveCell
>
> .Value =3D Now
> .NumberFormat =3D "mm/dd/yy h:mm AM/PM"
> =20
> If Now Mod 1 > 17 / 24 Then ' 17/24 =3D 5:00 PM
> mMsg =3D MsgBox(mPrompt, mBoxStyle, mTitle)
> .ClearContents
> End If
> =20
>End With
>
>End Sub
>
>---
>HTH
>Jason
>Atlanta, GA
>
>>-----Original Message-----
>>Geez--this is my second post of the morning. Sorry about=20
>not thinking my=20
>>queries through all the way!
>>
>>Anyway, I have the following macro which deposits the=20
>date and time into a=20
>>cell:
>>
>>Sub DateAndTime()
>> With ActiveCell
>> .Value =3D Now
>> .NumberFormat =3D "mm/dd/yy h:mm AM/PM"
>> End With
>>End Sub
>>
>>Unfortunately, the stamp deposited into the cell seems=20
>not to be recognized=20
>>by Data Validation, which is set up to flash a warning=20
>box if the time entered=20
>>is after 5:00 PM. If I enter the identical info that the=20
>macro generates=20
>>manually and hit "Enter," THEN Validation pops the=20
>warning box up.
>>
>>What am I doing wrong? Can I rewrite the macro so that=20
>it essentially hits=20
>>"Enter" at the end so that validation will work?
>>
>>Thanks!=20
>>.
>>
>.
>