If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"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! |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 . |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Pressing the Enter Key | kbrad | Using Forms | 2 | February 23rd, 2005 12:13 PM |
Query / Form - user enter | Jorge | General Discussion | 0 | November 22nd, 2004 02:13 PM |
How do I totally remove macros from a Workbook? | Amanda Battist | Worksheet Functions | 2 | September 27th, 2004 07:33 PM |
Making Macros available to other users? | John | Worksheet Functions | 7 | April 13th, 2004 08:19 AM |
Can I "sign" my own macros? or some other fix for the message about security and macros? | BruceJ | Worksheet Functions | 8 | November 6th, 2003 12:46 AM |