Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code For Date Accepts Text
Can someone tell me what is wrong here? Am asking the user to input a date
in the mm/dd/yy format. It works ok except that it accepts any text string that is entered (it rejects something like January 3, 2006 though). Obviously, I do not want it to accept text, and cannot figure out how to fix this. Any help would be appreciated....am sure it is something simple. Sub SWStartDate() Dim UserEntry As String Dim Msg As String Msg = "Enter the contract start date, in mm/dd/yy format; then click on 'OK'." Do UserEntry = InputBox(Msg) If UserEntry = "" Then MsgBox ("You must enter a response; please try again.") End If If UserEntry < "" Then If UserEntry = Format(UserEntry, "mm/dd/yy") Then Exit Do End If If UserEntry < Format(UserEntry, "mm/dd/yy") Then Msg = "Invalid entry; please enter a date in the mm/dd/yy format." End If End If Msg = "Invalid entry; please enter a date in the mm/dd/yy format." Loop Worksheets("Software Inventory").Range("C5").Value = UserEntry End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code For Date Accepts Text
Hi Paige
Use the VBA IsDate function to test if Msg is a date See VBA help for IsDate -- Regards Ron De Bruin http://www.rondebruin.nl "Paige" wrote in message ... Can someone tell me what is wrong here? Am asking the user to input a date in the mm/dd/yy format. It works ok except that it accepts any text string that is entered (it rejects something like January 3, 2006 though). Obviously, I do not want it to accept text, and cannot figure out how to fix this. Any help would be appreciated....am sure it is something simple. Sub SWStartDate() Dim UserEntry As String Dim Msg As String Msg = "Enter the contract start date, in mm/dd/yy format; then click on 'OK'." Do UserEntry = InputBox(Msg) If UserEntry = "" Then MsgBox ("You must enter a response; please try again.") End If If UserEntry < "" Then If UserEntry = Format(UserEntry, "mm/dd/yy") Then Exit Do End If If UserEntry < Format(UserEntry, "mm/dd/yy") Then Msg = "Invalid entry; please enter a date in the mm/dd/yy format." End If End If Msg = "Invalid entry; please enter a date in the mm/dd/yy format." Loop Worksheets("Software Inventory").Range("C5").Value = UserEntry End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code For Date Accepts Text
Works like a charm! Thanks so much Ron!
"Ron de Bruin" wrote: Hi Paige Use the VBA IsDate function to test if Msg is a date See VBA help for IsDate -- Regards Ron De Bruin http://www.rondebruin.nl "Paige" wrote in message ... Can someone tell me what is wrong here? Am asking the user to input a date in the mm/dd/yy format. It works ok except that it accepts any text string that is entered (it rejects something like January 3, 2006 though). Obviously, I do not want it to accept text, and cannot figure out how to fix this. Any help would be appreciated....am sure it is something simple. Sub SWStartDate() Dim UserEntry As String Dim Msg As String Msg = "Enter the contract start date, in mm/dd/yy format; then click on 'OK'." Do UserEntry = InputBox(Msg) If UserEntry = "" Then MsgBox ("You must enter a response; please try again.") End If If UserEntry < "" Then If UserEntry = Format(UserEntry, "mm/dd/yy") Then Exit Do End If If UserEntry < Format(UserEntry, "mm/dd/yy") Then Msg = "Invalid entry; please enter a date in the mm/dd/yy format." End If End If Msg = "Invalid entry; please enter a date in the mm/dd/yy format." Loop Worksheets("Software Inventory").Range("C5").Value = UserEntry End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create list validation from code without the text being converted into date format? | Excel Programming | |||
Textbox accepts alpha only | Excel Programming | |||
TextBox that only accepts integers? | Excel Programming | |||
code to convert date from TEXT format (03-02) to DATE format (200203) | Excel Programming | |||
Date problem from code to text box to spreadsheet | Excel Programming |