Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Entry through input prompt
I have a macro working which populates a table with user data entry with
input prompts. The data needs to be validated. Particularly one cell with date input. When entering directly into cell the validation rules work and an error message is generated for invalid data. But when entered through "Input prompts" the invalid data is accepted. Any way around this?? Please help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Entry through input prompt
Not sure how much "validation" you want to do on what is entered, but this
asks for a date (and puts in the current date as the default date in the input box), then inserts the user response into E15 and formats. If you don't want a default date in the input box, then where it says "USERENTRY = InputBox...", remove the Format(Now, "mm/dd/yyyy") part of that line. Also, you can change how you want the date formatted by changing, for example, mm/dd/yyyy to mm/dd/yy. If you want more "validation" that this, then one of the more experienced folks will have to help. Hope this is useful. Sub AskForDate() Dim MSG As String Dim USERENTRY As String Dim DATEVALUE As Boolean If Worksheets("Input and P&L").Range("E15").Value = "" Then DATEVALUE = IsDate(USERENTRY) MSG = "There is no date in the 'Date Prepared' field on the Input and P&L tab. If you want to use the current date, then just click on 'OK'. If not, enter another date below (in mm/dd/yyyy or m/d/yy format), then click on 'OK'." Do USERENTRY = InputBox(MSG, "PLEASE ENTER THE 'DATE PREPARED'", Format(Now, "mm/dd/yyyy")) If USERENTRY = "" Then MsgBox ("You must enter a response; please try again.") End If If IsDate(USERENTRY) = True Then Exit Do End If Loop Worksheets("Input and P&L").Range("E15").Value = USERENTRY Worksheets("Input and P&L").Range("E15").NumberFormat = "mm/dd/yyyy;@" End Sub "a m spock" wrote: I have a macro working which populates a table with user data entry with input prompts. The data needs to be validated. Particularly one cell with date input. When entering directly into cell the validation rules work and an error message is generated for invalid data. But when entered through "Input prompts" the invalid data is accepted. Any way around this?? Please help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Entry through input prompt
Many thanks. We are almost there. But being a novice at this, I need your
help to take the next and final step. The code I presently have in the macro is: ActiveCell.Select ActiveCell.Value = InputBox("Enter Date as DD-MM-YYYY.") The validation rule: is a date between 15-08-1947 and "Today". I have alreday set this validation rule in the ActiveCell, but seems to be useless. Default date is not needed. But ideally, though not necessarily, a greyed out format indicator, if feasible, would be great. The error message needed in case of blank or invalid date : ""Please enter a date between 15-08-1947 and Today" Could you help with a sub which I can call from within another macro to accomplish this? I hope I have expressed clearly! "Paige" wrote: Not sure how much "validation" you want to do on what is entered, but this asks for a date (and puts in the current date as the default date in the input box), then inserts the user response into E15 and formats. If you don't want a default date in the input box, then where it says "USERENTRY = InputBox...", remove the Format(Now, "mm/dd/yyyy") part of that line. Also, you can change how you want the date formatted by changing, for example, mm/dd/yyyy to mm/dd/yy. If you want more "validation" that this, then one of the more experienced folks will have to help. Hope this is useful. Sub AskForDate() Dim MSG As String Dim USERENTRY As String Dim DATEVALUE As Boolean If Worksheets("Input and P&L").Range("E15").Value = "" Then DATEVALUE = IsDate(USERENTRY) MSG = "There is no date in the 'Date Prepared' field on the Input and P&L tab. If you want to use the current date, then just click on 'OK'. If not, enter another date below (in mm/dd/yyyy or m/d/yy format), then click on 'OK'." Do USERENTRY = InputBox(MSG, "PLEASE ENTER THE 'DATE PREPARED'", Format(Now, "mm/dd/yyyy")) If USERENTRY = "" Then MsgBox ("You must enter a response; please try again.") End If If IsDate(USERENTRY) = True Then Exit Do End If Loop Worksheets("Input and P&L").Range("E15").Value = USERENTRY Worksheets("Input and P&L").Range("E15").NumberFormat = "mm/dd/yyyy;@" End Sub "a m spock" wrote: I have a macro working which populates a table with user data entry with input prompts. The data needs to be validated. Particularly one cell with date input. When entering directly into cell the validation rules work and an error message is generated for invalid data. But when entered through "Input prompts" the invalid data is accepted. Any way around this?? Please help. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Entry through input prompt
I also need to make sure that any data other than the DD-MM-YYYY is not
accepted. For example in the macro you recommended the entry 5 is accepted as a valid entry 05-01-1900 "Paige" wrote: Not sure how much "validation" you want to do on what is entered, but this asks for a date (and puts in the current date as the default date in the input box), then inserts the user response into E15 and formats. If you don't want a default date in the input box, then where it says "USERENTRY = InputBox...", remove the Format(Now, "mm/dd/yyyy") part of that line. Also, you can change how you want the date formatted by changing, for example, mm/dd/yyyy to mm/dd/yy. If you want more "validation" that this, then one of the more experienced folks will have to help. Hope this is useful. Sub AskForDate() Dim MSG As String Dim USERENTRY As String Dim DATEVALUE As Boolean If Worksheets("Input and P&L").Range("E15").Value = "" Then DATEVALUE = IsDate(USERENTRY) MSG = "There is no date in the 'Date Prepared' field on the Input and P&L tab. If you want to use the current date, then just click on 'OK'. If not, enter another date below (in mm/dd/yyyy or m/d/yy format), then click on 'OK'." Do USERENTRY = InputBox(MSG, "PLEASE ENTER THE 'DATE PREPARED'", Format(Now, "mm/dd/yyyy")) If USERENTRY = "" Then MsgBox ("You must enter a response; please try again.") End If If IsDate(USERENTRY) = True Then Exit Do End If Loop Worksheets("Input and P&L").Range("E15").Value = USERENTRY Worksheets("Input and P&L").Range("E15").NumberFormat = "mm/dd/yyyy;@" End Sub "a m spock" wrote: I have a macro working which populates a table with user data entry with input prompts. The data needs to be validated. Particularly one cell with date input. When entering directly into cell the validation rules work and an error message is generated for invalid data. But when entered through "Input prompts" the invalid data is accepted. Any way around this?? Please help. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Entry through input prompt
My friend, I am happy to report some progress:
Working with your code I have written the following. Which works for entering and formatting the date. But it still accepts incomplete input and completes it automatically to the nearest guess. Can this be prevented so that only completed data is accepted? Sub AskForDate() Dim MSG As String Dim USERENTRY As String Dim DATEVALUE As Boolean If ActiveCell.Value = "" Then DATEVALUE = IsDate(USERENTRY) MSG = "There is no date in the 'Date' field. If you want to use the current date, then just click on 'OK'. If not, enter another date below (in dd/mm/yyyy format), then click on" 'OK'." Do USERENTRY = InputBox(MSG, "PLEASE ENTER THE 'DATE'", Format(Now, "dd/mm/yyyy")) If USERENTRY = "" Then MsgBox ("You must enter a response; please try again.") End If If IsDate(USERENTRY) = True Then End If Exit Do Loop ActiveCell.Value = USERENTRY ActiveCell.NumberFormat = "dd/mm/yyyy;@" End If End Sub NOTE: I had to add one more End If before End Sub to make it work error free. "Paige" wrote: Not sure how much "validation" you want to do on what is entered, but this asks for a date (and puts in the current date as the default date in the input box), then inserts the user response into E15 and formats. If you don't want a default date in the input box, then where it says "USERENTRY = InputBox...", remove the Format(Now, "mm/dd/yyyy") part of that line. Also, you can change how you want the date formatted by changing, for example, mm/dd/yyyy to mm/dd/yy. If you want more "validation" that this, then one of the more experienced folks will have to help. Hope this is useful. Sub AskForDate() Dim MSG As String Dim USERENTRY As String Dim DATEVALUE As Boolean If Worksheets("Input and P&L").Range("E15").Value = "" Then DATEVALUE = IsDate(USERENTRY) MSG = "There is no date in the 'Date Prepared' field on the Input and P&L tab. If you want to use the current date, then just click on 'OK'. If not, enter another date below (in mm/dd/yyyy or m/d/yy format), then click on 'OK'." Do USERENTRY = InputBox(MSG, "PLEASE ENTER THE 'DATE PREPARED'", Format(Now, "mm/dd/yyyy")) If USERENTRY = "" Then MsgBox ("You must enter a response; please try again.") End If If IsDate(USERENTRY) = True Then Exit Do End If Loop Worksheets("Input and P&L").Range("E15").Value = USERENTRY Worksheets("Input and P&L").Range("E15").NumberFormat = "mm/dd/yyyy;@" End Sub "a m spock" wrote: I have a macro working which populates a table with user data entry with input prompts. The data needs to be validated. Particularly one cell with date input. When entering directly into cell the validation rules work and an error message is generated for invalid data. But when entered through "Input prompts" the invalid data is accepted. Any way around this?? Please help. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Entry through input prompt
Try this; even if they put mm/dd/yy (or other) format in the box, it will
correct the format when entered into the cell. Sub AskForDate2() Dim MSG As String Dim USERENTRY As String Dim DATEVALUE As Date Dim MINDATEVAL As Date Dim MAXDATEVAL As Date MINDATEVAL = DateSerial(1947, 8, 15) MAXDATEVAL = Format(Now, "dd/mm/yyyy") If ActiveCell.Value = "" Then DATEVALUE = IsDate(USERENTRY) MSG = "There is no date in the 'Date' field. If you want to use the current " & _ "date, then just click on 'OK'. If not, then enter another date below (in " & _ "dd/mm/yyyy format), then click on 'OK'." Do USERENTRY = InputBox(MSG, "PLEASE ENTER THE 'DATE'", Format(Now, "dd/mm/yyyy")) If USERENTRY = "" Then MsgBox ("You must enter a response; please try again.") End If If IsDate(USERENTRY) = True Then If USERENTRY = MINDATEVAL And USERENTRY <= MAXDATEVAL Then Exit Do End If If USERENTRY < MINDATEVAL Or USERENTRY MAXDATEVAL Then MSG = "Your previous entry was invalid. Please enter a value between " & Format(MINDATEVAL, "dd/mm/yyyy") & " and " & Format(MAXDATEVAL, "dd/mm/yyyy") & "." End If End If Loop ActiveCell.Value = USERENTRY ActiveCell.NumberFormat = "dd/mm/yyyy;@" End If End Sub "a m spock" wrote: My friend, I am happy to report some progress: Working with your code I have written the following. Which works for entering and formatting the date. But it still accepts incomplete input and completes it automatically to the nearest guess. Can this be prevented so that only completed data is accepted? Sub AskForDate() Dim MSG As String Dim USERENTRY As String Dim DATEVALUE As Boolean If ActiveCell.Value = "" Then DATEVALUE = IsDate(USERENTRY) MSG = "There is no date in the 'Date' field. If you want to use the current date, then just click on 'OK'. If not, enter another date below (in dd/mm/yyyy format), then click on" 'OK'." Do USERENTRY = InputBox(MSG, "PLEASE ENTER THE 'DATE'", Format(Now, "dd/mm/yyyy")) If USERENTRY = "" Then MsgBox ("You must enter a response; please try again.") End If If IsDate(USERENTRY) = True Then End If Exit Do Loop ActiveCell.Value = USERENTRY ActiveCell.NumberFormat = "dd/mm/yyyy;@" End If End Sub NOTE: I had to add one more End If before End Sub to make it work error free. "Paige" wrote: Not sure how much "validation" you want to do on what is entered, but this asks for a date (and puts in the current date as the default date in the input box), then inserts the user response into E15 and formats. If you don't want a default date in the input box, then where it says "USERENTRY = InputBox...", remove the Format(Now, "mm/dd/yyyy") part of that line. Also, you can change how you want the date formatted by changing, for example, mm/dd/yyyy to mm/dd/yy. If you want more "validation" that this, then one of the more experienced folks will have to help. Hope this is useful. Sub AskForDate() Dim MSG As String Dim USERENTRY As String Dim DATEVALUE As Boolean If Worksheets("Input and P&L").Range("E15").Value = "" Then DATEVALUE = IsDate(USERENTRY) MSG = "There is no date in the 'Date Prepared' field on the Input and P&L tab. If you want to use the current date, then just click on 'OK'. If not, enter another date below (in mm/dd/yyyy or m/d/yy format), then click on 'OK'." Do USERENTRY = InputBox(MSG, "PLEASE ENTER THE 'DATE PREPARED'", Format(Now, "mm/dd/yyyy")) If USERENTRY = "" Then MsgBox ("You must enter a response; please try again.") End If If IsDate(USERENTRY) = True Then Exit Do End If Loop Worksheets("Input and P&L").Range("E15").Value = USERENTRY Worksheets("Input and P&L").Range("E15").NumberFormat = "mm/dd/yyyy;@" End Sub "a m spock" wrote: I have a macro working which populates a table with user data entry with input prompts. The data needs to be validated. Particularly one cell with date input. When entering directly into cell the validation rules work and an error message is generated for invalid data. But when entered through "Input prompts" the invalid data is accepted. Any way around this?? Please help. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Entry through input prompt
Many thanks. It does not now accept single digit or two digit date only
entry. However: 1. It somehow still accepts date and month only entry, and 2. Manages to switch date and month if date <=12. "Paige" wrote: Try this; even if they put mm/dd/yy (or other) format in the box, it will correct the format when entered into the cell. Sub AskForDate2() Dim MSG As String Dim USERENTRY As String Dim DATEVALUE As Date Dim MINDATEVAL As Date Dim MAXDATEVAL As Date MINDATEVAL = DateSerial(1947, 8, 15) MAXDATEVAL = Format(Now, "dd/mm/yyyy") If ActiveCell.Value = "" Then DATEVALUE = IsDate(USERENTRY) MSG = "There is no date in the 'Date' field. If you want to use the current " & _ "date, then just click on 'OK'. If not, then enter another date below (in " & _ "dd/mm/yyyy format), then click on 'OK'." Do USERENTRY = InputBox(MSG, "PLEASE ENTER THE 'DATE'", Format(Now, "dd/mm/yyyy")) If USERENTRY = "" Then MsgBox ("You must enter a response; please try again.") End If If IsDate(USERENTRY) = True Then If USERENTRY = MINDATEVAL And USERENTRY <= MAXDATEVAL Then Exit Do End If If USERENTRY < MINDATEVAL Or USERENTRY MAXDATEVAL Then MSG = "Your previous entry was invalid. Please enter a value between " & Format(MINDATEVAL, "dd/mm/yyyy") & " and " & Format(MAXDATEVAL, "dd/mm/yyyy") & "." End If End If Loop ActiveCell.Value = USERENTRY ActiveCell.NumberFormat = "dd/mm/yyyy;@" End If End Sub "a m spock" wrote: My friend, I am happy to report some progress: Working with your code I have written the following. Which works for entering and formatting the date. But it still accepts incomplete input and completes it automatically to the nearest guess. Can this be prevented so that only completed data is accepted? Sub AskForDate() Dim MSG As String Dim USERENTRY As String Dim DATEVALUE As Boolean If ActiveCell.Value = "" Then DATEVALUE = IsDate(USERENTRY) MSG = "There is no date in the 'Date' field. If you want to use the current date, then just click on 'OK'. If not, enter another date below (in dd/mm/yyyy format), then click on" 'OK'." Do USERENTRY = InputBox(MSG, "PLEASE ENTER THE 'DATE'", Format(Now, "dd/mm/yyyy")) If USERENTRY = "" Then MsgBox ("You must enter a response; please try again.") End If If IsDate(USERENTRY) = True Then End If Exit Do Loop ActiveCell.Value = USERENTRY ActiveCell.NumberFormat = "dd/mm/yyyy;@" End If End Sub NOTE: I had to add one more End If before End Sub to make it work error free. "Paige" wrote: Not sure how much "validation" you want to do on what is entered, but this asks for a date (and puts in the current date as the default date in the input box), then inserts the user response into E15 and formats. If you don't want a default date in the input box, then where it says "USERENTRY = InputBox...", remove the Format(Now, "mm/dd/yyyy") part of that line. Also, you can change how you want the date formatted by changing, for example, mm/dd/yyyy to mm/dd/yy. If you want more "validation" that this, then one of the more experienced folks will have to help. Hope this is useful. Sub AskForDate() Dim MSG As String Dim USERENTRY As String Dim DATEVALUE As Boolean If Worksheets("Input and P&L").Range("E15").Value = "" Then DATEVALUE = IsDate(USERENTRY) MSG = "There is no date in the 'Date Prepared' field on the Input and P&L tab. If you want to use the current date, then just click on 'OK'. If not, enter another date below (in mm/dd/yyyy or m/d/yy format), then click on 'OK'." Do USERENTRY = InputBox(MSG, "PLEASE ENTER THE 'DATE PREPARED'", Format(Now, "mm/dd/yyyy")) If USERENTRY = "" Then MsgBox ("You must enter a response; please try again.") End If If IsDate(USERENTRY) = True Then Exit Do End If Loop Worksheets("Input and P&L").Range("E15").Value = USERENTRY Worksheets("Input and P&L").Range("E15").NumberFormat = "mm/dd/yyyy;@" End Sub "a m spock" wrote: I have a macro working which populates a table with user data entry with input prompts. The data needs to be validated. Particularly one cell with date input. When entering directly into cell the validation rules work and an error message is generated for invalid data. But when entered through "Input prompts" the invalid data is accepted. Any way around this?? Please help. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Entry through input prompt
Well, this is beyond my capability, since I'm self-taught. If you ask them
to put it in as dd/mm/yyyy but they put it in as mm/dd/yyyy instead, then there is no way to know in many cases. For example, they can enter 05/01/2008, but we don't know if they actually meant May 1, 2008 or January 5, 2008. Does this make sense? "a m spock" wrote: Many thanks. It does not now accept single digit or two digit date only entry. However: 1. It somehow still accepts date and month only entry, and 2. Manages to switch date and month if date <=12. "Paige" wrote: Try this; even if they put mm/dd/yy (or other) format in the box, it will correct the format when entered into the cell. Sub AskForDate2() Dim MSG As String Dim USERENTRY As String Dim DATEVALUE As Date Dim MINDATEVAL As Date Dim MAXDATEVAL As Date MINDATEVAL = DateSerial(1947, 8, 15) MAXDATEVAL = Format(Now, "dd/mm/yyyy") If ActiveCell.Value = "" Then DATEVALUE = IsDate(USERENTRY) MSG = "There is no date in the 'Date' field. If you want to use the current " & _ "date, then just click on 'OK'. If not, then enter another date below (in " & _ "dd/mm/yyyy format), then click on 'OK'." Do USERENTRY = InputBox(MSG, "PLEASE ENTER THE 'DATE'", Format(Now, "dd/mm/yyyy")) If USERENTRY = "" Then MsgBox ("You must enter a response; please try again.") End If If IsDate(USERENTRY) = True Then If USERENTRY = MINDATEVAL And USERENTRY <= MAXDATEVAL Then Exit Do End If If USERENTRY < MINDATEVAL Or USERENTRY MAXDATEVAL Then MSG = "Your previous entry was invalid. Please enter a value between " & Format(MINDATEVAL, "dd/mm/yyyy") & " and " & Format(MAXDATEVAL, "dd/mm/yyyy") & "." End If End If Loop ActiveCell.Value = USERENTRY ActiveCell.NumberFormat = "dd/mm/yyyy;@" End If End Sub "a m spock" wrote: My friend, I am happy to report some progress: Working with your code I have written the following. Which works for entering and formatting the date. But it still accepts incomplete input and completes it automatically to the nearest guess. Can this be prevented so that only completed data is accepted? Sub AskForDate() Dim MSG As String Dim USERENTRY As String Dim DATEVALUE As Boolean If ActiveCell.Value = "" Then DATEVALUE = IsDate(USERENTRY) MSG = "There is no date in the 'Date' field. If you want to use the current date, then just click on 'OK'. If not, enter another date below (in dd/mm/yyyy format), then click on" 'OK'." Do USERENTRY = InputBox(MSG, "PLEASE ENTER THE 'DATE'", Format(Now, "dd/mm/yyyy")) If USERENTRY = "" Then MsgBox ("You must enter a response; please try again.") End If If IsDate(USERENTRY) = True Then End If Exit Do Loop ActiveCell.Value = USERENTRY ActiveCell.NumberFormat = "dd/mm/yyyy;@" End If End Sub NOTE: I had to add one more End If before End Sub to make it work error free. "Paige" wrote: Not sure how much "validation" you want to do on what is entered, but this asks for a date (and puts in the current date as the default date in the input box), then inserts the user response into E15 and formats. If you don't want a default date in the input box, then where it says "USERENTRY = InputBox...", remove the Format(Now, "mm/dd/yyyy") part of that line. Also, you can change how you want the date formatted by changing, for example, mm/dd/yyyy to mm/dd/yy. If you want more "validation" that this, then one of the more experienced folks will have to help. Hope this is useful. Sub AskForDate() Dim MSG As String Dim USERENTRY As String Dim DATEVALUE As Boolean If Worksheets("Input and P&L").Range("E15").Value = "" Then DATEVALUE = IsDate(USERENTRY) MSG = "There is no date in the 'Date Prepared' field on the Input and P&L tab. If you want to use the current date, then just click on 'OK'. If not, enter another date below (in mm/dd/yyyy or m/d/yy format), then click on 'OK'." Do USERENTRY = InputBox(MSG, "PLEASE ENTER THE 'DATE PREPARED'", Format(Now, "mm/dd/yyyy")) If USERENTRY = "" Then MsgBox ("You must enter a response; please try again.") End If If IsDate(USERENTRY) = True Then Exit Do End If Loop Worksheets("Input and P&L").Range("E15").Value = USERENTRY Worksheets("Input and P&L").Range("E15").NumberFormat = "mm/dd/yyyy;@" End Sub "a m spock" wrote: I have a macro working which populates a table with user data entry with input prompts. The data needs to be validated. Particularly one cell with date input. When entering directly into cell the validation rules work and an error message is generated for invalid data. But when entered through "Input prompts" the invalid data is accepted. Any way around this?? Please help. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Entry through input prompt
Thanks for trying anyway. What it does is if I enter 05/06/2008, it returns
06/05/2008 and vice versa!! This is crazy but is obviously an issue between VBA and Excel. There might be a bypass which as you say needs more expertise. Once again, thanks. "Paige" wrote: Well, this is beyond my capability, since I'm self-taught. If you ask them to put it in as dd/mm/yyyy but they put it in as mm/dd/yyyy instead, then there is no way to know in many cases. For example, they can enter 05/01/2008, but we don't know if they actually meant May 1, 2008 or January 5, 2008. Does this make sense? "a m spock" wrote: Many thanks. It does not now accept single digit or two digit date only entry. However: 1. It somehow still accepts date and month only entry, and 2. Manages to switch date and month if date <=12. "Paige" wrote: Try this; even if they put mm/dd/yy (or other) format in the box, it will correct the format when entered into the cell. Sub AskForDate2() Dim MSG As String Dim USERENTRY As String Dim DATEVALUE As Date Dim MINDATEVAL As Date Dim MAXDATEVAL As Date MINDATEVAL = DateSerial(1947, 8, 15) MAXDATEVAL = Format(Now, "dd/mm/yyyy") If ActiveCell.Value = "" Then DATEVALUE = IsDate(USERENTRY) MSG = "There is no date in the 'Date' field. If you want to use the current " & _ "date, then just click on 'OK'. If not, then enter another date below (in " & _ "dd/mm/yyyy format), then click on 'OK'." Do USERENTRY = InputBox(MSG, "PLEASE ENTER THE 'DATE'", Format(Now, "dd/mm/yyyy")) If USERENTRY = "" Then MsgBox ("You must enter a response; please try again.") End If If IsDate(USERENTRY) = True Then If USERENTRY = MINDATEVAL And USERENTRY <= MAXDATEVAL Then Exit Do End If If USERENTRY < MINDATEVAL Or USERENTRY MAXDATEVAL Then MSG = "Your previous entry was invalid. Please enter a value between " & Format(MINDATEVAL, "dd/mm/yyyy") & " and " & Format(MAXDATEVAL, "dd/mm/yyyy") & "." End If End If Loop ActiveCell.Value = USERENTRY ActiveCell.NumberFormat = "dd/mm/yyyy;@" End If End Sub "a m spock" wrote: My friend, I am happy to report some progress: Working with your code I have written the following. Which works for entering and formatting the date. But it still accepts incomplete input and completes it automatically to the nearest guess. Can this be prevented so that only completed data is accepted? Sub AskForDate() Dim MSG As String Dim USERENTRY As String Dim DATEVALUE As Boolean If ActiveCell.Value = "" Then DATEVALUE = IsDate(USERENTRY) MSG = "There is no date in the 'Date' field. If you want to use the current date, then just click on 'OK'. If not, enter another date below (in dd/mm/yyyy format), then click on" 'OK'." Do USERENTRY = InputBox(MSG, "PLEASE ENTER THE 'DATE'", Format(Now, "dd/mm/yyyy")) If USERENTRY = "" Then MsgBox ("You must enter a response; please try again.") End If If IsDate(USERENTRY) = True Then End If Exit Do Loop ActiveCell.Value = USERENTRY ActiveCell.NumberFormat = "dd/mm/yyyy;@" End If End Sub NOTE: I had to add one more End If before End Sub to make it work error free. "Paige" wrote: Not sure how much "validation" you want to do on what is entered, but this asks for a date (and puts in the current date as the default date in the input box), then inserts the user response into E15 and formats. If you don't want a default date in the input box, then where it says "USERENTRY = InputBox...", remove the Format(Now, "mm/dd/yyyy") part of that line. Also, you can change how you want the date formatted by changing, for example, mm/dd/yyyy to mm/dd/yy. If you want more "validation" that this, then one of the more experienced folks will have to help. Hope this is useful. Sub AskForDate() Dim MSG As String Dim USERENTRY As String Dim DATEVALUE As Boolean If Worksheets("Input and P&L").Range("E15").Value = "" Then DATEVALUE = IsDate(USERENTRY) MSG = "There is no date in the 'Date Prepared' field on the Input and P&L tab. If you want to use the current date, then just click on 'OK'. If not, enter another date below (in mm/dd/yyyy or m/d/yy format), then click on 'OK'." Do USERENTRY = InputBox(MSG, "PLEASE ENTER THE 'DATE PREPARED'", Format(Now, "mm/dd/yyyy")) If USERENTRY = "" Then MsgBox ("You must enter a response; please try again.") End If If IsDate(USERENTRY) = True Then Exit Do End If Loop Worksheets("Input and P&L").Range("E15").Value = USERENTRY Worksheets("Input and P&L").Range("E15").NumberFormat = "mm/dd/yyyy;@" End Sub "a m spock" wrote: I have a macro working which populates a table with user data entry with input prompts. The data needs to be validated. Particularly one cell with date input. When entering directly into cell the validation rules work and an error message is generated for invalid data. But when entered through "Input prompts" the invalid data is accepted. Any way around this?? Please help. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Entry through input prompt
Maybe it would be easier to ask them to enter the date as it is normally done
- mm/dd/yyyy, then have Excel format the entry as dd/mm/yyyy. But again, this assumes they DO enter mm/dd/yyyy; if they accidentally enter dd/mm/yyyy, Excel will think they entered mm/dd/yyyy and the result will be reversed; i.e., they enter 05/03/2008 meaning March 5, Excel will reverse it to 03/05/2008. "a m spock" wrote: Thanks for trying anyway. What it does is if I enter 05/06/2008, it returns 06/05/2008 and vice versa!! This is crazy but is obviously an issue between VBA and Excel. There might be a bypass which as you say needs more expertise. Once again, thanks. "Paige" wrote: Well, this is beyond my capability, since I'm self-taught. If you ask them to put it in as dd/mm/yyyy but they put it in as mm/dd/yyyy instead, then there is no way to know in many cases. For example, they can enter 05/01/2008, but we don't know if they actually meant May 1, 2008 or January 5, 2008. Does this make sense? "a m spock" wrote: Many thanks. It does not now accept single digit or two digit date only entry. However: 1. It somehow still accepts date and month only entry, and 2. Manages to switch date and month if date <=12. "Paige" wrote: Try this; even if they put mm/dd/yy (or other) format in the box, it will correct the format when entered into the cell. Sub AskForDate2() Dim MSG As String Dim USERENTRY As String Dim DATEVALUE As Date Dim MINDATEVAL As Date Dim MAXDATEVAL As Date MINDATEVAL = DateSerial(1947, 8, 15) MAXDATEVAL = Format(Now, "dd/mm/yyyy") If ActiveCell.Value = "" Then DATEVALUE = IsDate(USERENTRY) MSG = "There is no date in the 'Date' field. If you want to use the current " & _ "date, then just click on 'OK'. If not, then enter another date below (in " & _ "dd/mm/yyyy format), then click on 'OK'." Do USERENTRY = InputBox(MSG, "PLEASE ENTER THE 'DATE'", Format(Now, "dd/mm/yyyy")) If USERENTRY = "" Then MsgBox ("You must enter a response; please try again.") End If If IsDate(USERENTRY) = True Then If USERENTRY = MINDATEVAL And USERENTRY <= MAXDATEVAL Then Exit Do End If If USERENTRY < MINDATEVAL Or USERENTRY MAXDATEVAL Then MSG = "Your previous entry was invalid. Please enter a value between " & Format(MINDATEVAL, "dd/mm/yyyy") & " and " & Format(MAXDATEVAL, "dd/mm/yyyy") & "." End If End If Loop ActiveCell.Value = USERENTRY ActiveCell.NumberFormat = "dd/mm/yyyy;@" End If End Sub "a m spock" wrote: My friend, I am happy to report some progress: Working with your code I have written the following. Which works for entering and formatting the date. But it still accepts incomplete input and completes it automatically to the nearest guess. Can this be prevented so that only completed data is accepted? Sub AskForDate() Dim MSG As String Dim USERENTRY As String Dim DATEVALUE As Boolean If ActiveCell.Value = "" Then DATEVALUE = IsDate(USERENTRY) MSG = "There is no date in the 'Date' field. If you want to use the current date, then just click on 'OK'. If not, enter another date below (in dd/mm/yyyy format), then click on" 'OK'." Do USERENTRY = InputBox(MSG, "PLEASE ENTER THE 'DATE'", Format(Now, "dd/mm/yyyy")) If USERENTRY = "" Then MsgBox ("You must enter a response; please try again.") End If If IsDate(USERENTRY) = True Then End If Exit Do Loop ActiveCell.Value = USERENTRY ActiveCell.NumberFormat = "dd/mm/yyyy;@" End If End Sub NOTE: I had to add one more End If before End Sub to make it work error free. "Paige" wrote: Not sure how much "validation" you want to do on what is entered, but this asks for a date (and puts in the current date as the default date in the input box), then inserts the user response into E15 and formats. If you don't want a default date in the input box, then where it says "USERENTRY = InputBox...", remove the Format(Now, "mm/dd/yyyy") part of that line. Also, you can change how you want the date formatted by changing, for example, mm/dd/yyyy to mm/dd/yy. If you want more "validation" that this, then one of the more experienced folks will have to help. Hope this is useful. Sub AskForDate() Dim MSG As String Dim USERENTRY As String Dim DATEVALUE As Boolean If Worksheets("Input and P&L").Range("E15").Value = "" Then DATEVALUE = IsDate(USERENTRY) MSG = "There is no date in the 'Date Prepared' field on the Input and P&L tab. If you want to use the current date, then just click on 'OK'. If not, enter another date below (in mm/dd/yyyy or m/d/yy format), then click on 'OK'." Do USERENTRY = InputBox(MSG, "PLEASE ENTER THE 'DATE PREPARED'", Format(Now, "mm/dd/yyyy")) If USERENTRY = "" Then MsgBox ("You must enter a response; please try again.") End If If IsDate(USERENTRY) = True Then Exit Do End If Loop Worksheets("Input and P&L").Range("E15").Value = USERENTRY Worksheets("Input and P&L").Range("E15").NumberFormat = "mm/dd/yyyy;@" End Sub "a m spock" wrote: I have a macro working which populates a table with user data entry with input prompts. The data needs to be validated. Particularly one cell with date input. When entering directly into cell the validation rules work and an error message is generated for invalid data. But when entered through "Input prompts" the invalid data is accepted. Any way around this?? Please help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prompt user for input | Excel Programming | |||
Prompt user for input and utilize that input | Excel Worksheet Functions | |||
How can I have an input form to add a random no. to each input entry? | Excel Discussion (Misc queries) | |||
Having a Macro Prompt Me For Input | Excel Worksheet Functions | |||
Prompt for text entry in Cell B2 | Excel Programming |