"Enter" in Macros
 

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

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

Since the information is input without using the "Enter" key, however, =
my=20
Data Validation formatting doesn't respond to it. (I have the validation =
set up=20
to flash a warning box if the time isn't between 9:00 AM and 5:00 PM.) =
If I=20
click into the cell affter the macro is run and hit "Enter," the =
validation kicks=20
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=20
run? =20

Thanks and cheers!

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 wrote:

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

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!
-----Original Message-----
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 wrote:

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
.

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 wrote:

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!
-----Original Message-----
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 wrote:

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
.


--

Dave Peterson

Thanks, Dave--got it! I'll give it a shot first thing in
the morning. I appreciate your help a lot!
-----Original Message-----
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 wrote:

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!
-----Original Message-----
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 wrote:

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
.


--

Dave Peterson
.