"Enter" in Macros

[Desperate]

The following macro inputs the current date and time into one cell:

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

Since the information is input without using the "Enter" key, however, my
Data Validation formatting doesn't respond to it. (I have the validation set up
to flash a warning box if the time isn't between 9:00 AM and 5:00 PM.) If I
click into the cell affter the macro is run and hit "Enter," the validation kicks
in, but that kind of defeats the point of having validation at all.

How do I rewrite the macro so that it hits "Enter," so to speak, after it has
run?

Thanks and cheers!

 

[Dave Peterson]

I think I'd just build the validation into that routine.

Sub DateAndTime()
if now > timeserial(17,0,0) _
or now < timeserial(9,0,0) then
Msgbox "cannot use this macro!
else
With ActiveCell
.Value = Now
.NumberFormat = "mm/dd/yy h:mm AM/PM"
End With
end if
End Sub

 

[Desperate]

Hi, Dave--

I'm not sure if perhaps you aborted your post, but I only
get "Message Unavailable" when I click on your response.
Weird! Might I ask you to try reposting? Thanks!

 

[Dave Peterson]

It showed up ok for me--in fact, it was part of you response!

But here it is again:

I think I'd just build the validation into that routine.

Sub DateAndTime()
if now > timeserial(17,0,0) _
or now < timeserial(9,0,0) then
Msgbox "cannot use this macro!
else
With ActiveCell
.Value = Now
.NumberFormat = "mm/dd/yy h:mm AM/PM"
End With
end if
End Sub

 

[Desperate]

Thanks, Dave--got it! I'll give it a shot first thing in
the morning. I appreciate your help a lot!