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
.

.