Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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! . |
#3
|
|||
|
|||
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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Effect of Conditional Formatting, Data Validation | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Data Validation Window? | Excel Discussion (Misc queries) | |||
Using Validation List from Another Workbook with Dependent Data | Excel Worksheet Functions | |||
Excel2K: Is it possible to use dynamic named ranges in custom data validation formula? | Excel Discussion (Misc queries) |