Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation on text input boxes on a form
Hi
I was wondering if anyone can let me know how to add validation to text input boxes on forms? I have several forms on a spreadsheet that I am creating and there are quite a few time and date input boxes, which I was hoping I could add validation to so that the information is entered in the correct format? i.e. date should only be in dd/mm/yy, time should only be entered like hh:mm etc. Can this be done? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation on text input boxes on a form
Get the date as a String, and then detailed checks can be made:
Sub ordinate() Dim x As String Dim n As Integer Dim dt As Date x = Application.InputBox(prompt:="enter date mm/dd/yy", Type:=2) If Len(x) < 8 Then MsgBox ("bad input") End If s = Split(x, "/") If UBound(s) < 2 Then MsgBox ("bad input") End If n = s(0) If n 12 Then MsgBox ("bad input") End If n = s(1) If n 31 Then MsgBox ("bad input") End If dt = DateValue(x) MsgBox (dt) End Sub -- Gary''s Student - gsnu200826 "zak" wrote: Hi I was wondering if anyone can let me know how to add validation to text input boxes on forms? I have several forms on a spreadsheet that I am creating and there are quite a few time and date input boxes, which I was hoping I could add validation to so that the information is entered in the correct format? i.e. date should only be in dd/mm/yy, time should only be entered like hh:mm etc. Can this be done? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation on text input boxes on a form
There will be *no* way you can tell if the date is in dd/mm/yy or mm/dd/yy
format for month and day combinations less than or equal to 12. For example, if I entered 4/6/09 into your TextBox, how would *any* routine know whether I meant April 6th or June 4th? Your best bet is to use a calendar control of sort where your users can pick the date from a monthly display. -- Rick (MVP - Excel) "zak" wrote in message ... Hi I was wondering if anyone can let me know how to add validation to text input boxes on forms? I have several forms on a spreadsheet that I am creating and there are quite a few time and date input boxes, which I was hoping I could add validation to so that the information is entered in the correct format? i.e. date should only be in dd/mm/yy, time should only be entered like hh:mm etc. Can this be done? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation on text input boxes on a form
This is another possibility...
Sub ordinate() Dim x As String x = Application.InputBox(prompt:="enter date mm/dd/yy", Type:=2) If x Like "##/##/##" And IsDate(x) Then MsgBox DateValue(x) Else MsgBox "bad input" End If End Sub -- Rick (MVP - Excel) "Gary''s Student" wrote in message ... Get the date as a String, and then detailed checks can be made: Sub ordinate() Dim x As String Dim n As Integer Dim dt As Date x = Application.InputBox(prompt:="enter date mm/dd/yy", Type:=2) If Len(x) < 8 Then MsgBox ("bad input") End If s = Split(x, "/") If UBound(s) < 2 Then MsgBox ("bad input") End If n = s(0) If n 12 Then MsgBox ("bad input") End If n = s(1) If n 31 Then MsgBox ("bad input") End If dt = DateValue(x) MsgBox (dt) End Sub -- Gary''s Student - gsnu200826 "zak" wrote: Hi I was wondering if anyone can let me know how to add validation to text input boxes on forms? I have several forms on a spreadsheet that I am creating and there are quite a few time and date input boxes, which I was hoping I could add validation to so that the information is entered in the correct format? i.e. date should only be in dd/mm/yy, time should only be entered like hh:mm etc. Can this be done? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation on text input boxes on a form
I am wondering if the OP meant TextBox or InputBox since there is a reference
to a form. InputBox would normally be associated with the code proper, rather than a form, however, it could be in the code behind the form. Who knows? "Rick Rothstein" wrote: This is another possibility... Sub ordinate() Dim x As String x = Application.InputBox(prompt:="enter date mm/dd/yy", Type:=2) If x Like "##/##/##" And IsDate(x) Then MsgBox DateValue(x) Else MsgBox "bad input" End If End Sub -- Rick (MVP - Excel) "Gary''s Student" wrote in message ... Get the date as a String, and then detailed checks can be made: Sub ordinate() Dim x As String Dim n As Integer Dim dt As Date x = Application.InputBox(prompt:="enter date mm/dd/yy", Type:=2) If Len(x) < 8 Then MsgBox ("bad input") End If s = Split(x, "/") If UBound(s) < 2 Then MsgBox ("bad input") End If n = s(0) If n 12 Then MsgBox ("bad input") End If n = s(1) If n 31 Then MsgBox ("bad input") End If dt = DateValue(x) MsgBox (dt) End Sub -- Gary''s Student - gsnu200826 "zak" wrote: Hi I was wondering if anyone can let me know how to add validation to text input boxes on forms? I have several forms on a spreadsheet that I am creating and there are quite a few time and date input boxes, which I was hoping I could add validation to so that the information is entered in the correct format? i.e. date should only be in dd/mm/yy, time should only be entered like hh:mm etc. Can this be done? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation on text input boxes on a form
Yeah, I wasn't sure if the OP meant a worksheet designed as a "form" or an
actual UserForm. In any case, the type of validation he is looking for is not practical (see my direct posting to the OP)... I was just trying to show Gary''s Student an alternative to his Split approach. -- Rick (MVP - Excel) "JLGWhiz" wrote in message ... I am wondering if the OP meant TextBox or InputBox since there is a reference to a form. InputBox would normally be associated with the code proper, rather than a form, however, it could be in the code behind the form. Who knows? "Rick Rothstein" wrote: This is another possibility... Sub ordinate() Dim x As String x = Application.InputBox(prompt:="enter date mm/dd/yy", Type:=2) If x Like "##/##/##" And IsDate(x) Then MsgBox DateValue(x) Else MsgBox "bad input" End If End Sub -- Rick (MVP - Excel) "Gary''s Student" wrote in message ... Get the date as a String, and then detailed checks can be made: Sub ordinate() Dim x As String Dim n As Integer Dim dt As Date x = Application.InputBox(prompt:="enter date mm/dd/yy", Type:=2) If Len(x) < 8 Then MsgBox ("bad input") End If s = Split(x, "/") If UBound(s) < 2 Then MsgBox ("bad input") End If n = s(0) If n 12 Then MsgBox ("bad input") End If n = s(1) If n 31 Then MsgBox ("bad input") End If dt = DateValue(x) MsgBox (dt) End Sub -- Gary''s Student - gsnu200826 "zak" wrote: Hi I was wondering if anyone can let me know how to add validation to text input boxes on forms? I have several forms on a spreadsheet that I am creating and there are quite a few time and date input boxes, which I was hoping I could add validation to so that the information is entered in the correct format? i.e. date should only be in dd/mm/yy, time should only be entered like hh:mm etc. Can this be done? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation on text input boxes on a form
Hi Rick
I love *NO WAY* statements <gd&r. A date is a date is a date. Simplified (this is better done subclassing a textbox i an class module): Option Explicit Dim Dt1 As Date Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dt1 = 0 On Error Resume Next Dt1 = DateValue(Me.TextBox1.Text) On Error Goto 0 If Dt1 < 100 Then Me.TextBox1.Text = "" Else Me.TextBox1.Text = Format(Dt1, "d.mmmm yyyy") End If End Sub Best wishes Harald "Rick Rothstein" wrote in message ... There will be *no* way you can tell if the date is in dd/mm/yy or mm/dd/yy format for month and day combinations less than or equal to 12. For example, if I entered 4/6/09 into your TextBox, how would *any* routine know whether I meant April 6th or June 4th? Your best bet is to use a calendar control of sort where your users can pick the date from a monthly display. -- Rick (MVP - Excel) "zak" wrote in message ... Hi I was wondering if anyone can let me know how to add validation to text input boxes on forms? I have several forms on a spreadsheet that I am creating and there are quite a few time and date input boxes, which I was hoping I could add validation to so that the information is entered in the correct format? i.e. date should only be in dd/mm/yy, time should only be entered like hh:mm etc. Can this be done? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation on text input boxes on a form
Well, okay, that does give the user feedback allowing them to correct a bad
entry (assuming they look at it), but I still think a calendar control is a better way to go. By the way, after you enter a date and exit the TextBox, put your cursor back in the TextBox and then exit it without changing anything... you might want to use a more conventional date format to prevent that. -- Rick (MVP - Excel) "Harald Staff" wrote in message ... Hi Rick I love *NO WAY* statements <gd&r. A date is a date is a date. Simplified (this is better done subclassing a textbox i an class module): Option Explicit Dim Dt1 As Date Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dt1 = 0 On Error Resume Next Dt1 = DateValue(Me.TextBox1.Text) On Error Goto 0 If Dt1 < 100 Then Me.TextBox1.Text = "" Else Me.TextBox1.Text = Format(Dt1, "d.mmmm yyyy") End If End Sub Best wishes Harald "Rick Rothstein" wrote in message ... There will be *no* way you can tell if the date is in dd/mm/yy or mm/dd/yy format for month and day combinations less than or equal to 12. For example, if I entered 4/6/09 into your TextBox, how would *any* routine know whether I meant April 6th or June 4th? Your best bet is to use a calendar control of sort where your users can pick the date from a monthly display. -- Rick (MVP - Excel) "zak" wrote in message ... Hi I was wondering if anyone can let me know how to add validation to text input boxes on forms? I have several forms on a spreadsheet that I am creating and there are quite a few time and date input boxes, which I was hoping I could add validation to so that the information is entered in the correct format? i.e. date should only be in dd/mm/yy, time should only be entered like hh:mm etc. Can this be done? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation on text input boxes on a form
Sure, this is very quick and dirty. Date format should be read from the
regional settings, the one in this demo is pretty valid here in northern europe, so tabbing doesn't destroy it. It must of course be something Datevalue can read wherever one is. But it is pretty much the way a cell confirms a date entry; by altering the format slightly on exit. Best wishes Harald "Rick Rothstein" wrote in message ... Well, okay, that does give the user feedback allowing them to correct a bad entry (assuming they look at it), but I still think a calendar control is a better way to go. By the way, after you enter a date and exit the TextBox, put your cursor back in the TextBox and then exit it without changing anything... you might want to use a more conventional date format to prevent that. -- Rick (MVP - Excel) "Harald Staff" wrote in message ... Hi Rick I love *NO WAY* statements <gd&r. A date is a date is a date. Simplified (this is better done subclassing a textbox i an class module): Option Explicit Dim Dt1 As Date Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dt1 = 0 On Error Resume Next Dt1 = DateValue(Me.TextBox1.Text) On Error Goto 0 If Dt1 < 100 Then Me.TextBox1.Text = "" Else Me.TextBox1.Text = Format(Dt1, "d.mmmm yyyy") End If End Sub Best wishes Harald "Rick Rothstein" wrote in message ... There will be *no* way you can tell if the date is in dd/mm/yy or mm/dd/yy format for month and day combinations less than or equal to 12. For example, if I entered 4/6/09 into your TextBox, how would *any* routine know whether I meant April 6th or June 4th? Your best bet is to use a calendar control of sort where your users can pick the date from a monthly display. -- Rick (MVP - Excel) "zak" wrote in message ... Hi I was wondering if anyone can let me know how to add validation to text input boxes on forms? I have several forms on a spreadsheet that I am creating and there are quite a few time and date input boxes, which I was hoping I could add validation to so that the information is entered in the correct format? i.e. date should only be in dd/mm/yy, time should only be entered like hh:mm etc. Can this be done? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation on text input boxes on a form
You are correct. It is a great alternative!
The LIKE tests the format and IsDate() both checks and converts! A significant improvement over my shabby list of tests. -- Gary''s Student - gsnu200826 "Rick Rothstein" wrote: Yeah, I wasn't sure if the OP meant a worksheet designed as a "form" or an actual UserForm. In any case, the type of validation he is looking for is not practical (see my direct posting to the OP)... I was just trying to show Gary''s Student an alternative to his Split approach. -- Rick (MVP - Excel) "JLGWhiz" wrote in message ... I am wondering if the OP meant TextBox or InputBox since there is a reference to a form. InputBox would normally be associated with the code proper, rather than a form, however, it could be in the code behind the form. Who knows? "Rick Rothstein" wrote: This is another possibility... Sub ordinate() Dim x As String x = Application.InputBox(prompt:="enter date mm/dd/yy", Type:=2) If x Like "##/##/##" And IsDate(x) Then MsgBox DateValue(x) Else MsgBox "bad input" End If End Sub -- Rick (MVP - Excel) "Gary''s Student" wrote in message ... Get the date as a String, and then detailed checks can be made: Sub ordinate() Dim x As String Dim n As Integer Dim dt As Date x = Application.InputBox(prompt:="enter date mm/dd/yy", Type:=2) If Len(x) < 8 Then MsgBox ("bad input") End If s = Split(x, "/") If UBound(s) < 2 Then MsgBox ("bad input") End If n = s(0) If n 12 Then MsgBox ("bad input") End If n = s(1) If n 31 Then MsgBox ("bad input") End If dt = DateValue(x) MsgBox (dt) End Sub -- Gary''s Student - gsnu200826 "zak" wrote: Hi I was wondering if anyone can let me know how to add validation to text input boxes on forms? I have several forms on a spreadsheet that I am creating and there are quite a few time and date input boxes, which I was hoping I could add validation to so that the information is entered in the correct format? i.e. date should only be in dd/mm/yy, time should only be entered like hh:mm etc. Can this be done? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation on text input boxes on a form
Hi
Thanks for helping me out with this code. I actually have created a userform with a text input box (which is called PropDate). When the input box pops up and the date gets entered in the correct format - how can i add that date into the PropDate text input box on my userform? "Rick Rothstein" wrote: This is another possibility... Sub ordinate() Dim x As String x = Application.InputBox(prompt:="enter date mm/dd/yy", Type:=2) If x Like "##/##/##" And IsDate(x) Then MsgBox DateValue(x) Else MsgBox "bad input" End If End Sub -- Rick (MVP - Excel) "Gary''s Student" wrote in message ... Get the date as a String, and then detailed checks can be made: Sub ordinate() Dim x As String Dim n As Integer Dim dt As Date x = Application.InputBox(prompt:="enter date mm/dd/yy", Type:=2) If Len(x) < 8 Then MsgBox ("bad input") End If s = Split(x, "/") If UBound(s) < 2 Then MsgBox ("bad input") End If n = s(0) If n 12 Then MsgBox ("bad input") End If n = s(1) If n 31 Then MsgBox ("bad input") End If dt = DateValue(x) MsgBox (dt) End Sub -- Gary''s Student - gsnu200826 "zak" wrote: Hi I was wondering if anyone can let me know how to add validation to text input boxes on forms? I have several forms on a spreadsheet that I am creating and there are quite a few time and date input boxes, which I was hoping I could add validation to so that the information is entered in the correct format? i.e. date should only be in dd/mm/yy, time should only be entered like hh:mm etc. Can this be done? |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation on text input boxes on a form
I'm not exactly sure of how your interface is supposed to work. If you want
to use the InputBox method to get the date, here is that code (where I am assuming the TextBox is named PropDate)... Dim DateIn As String DateIn = Application.InputBox(prompt:="enter date mm/dd/yy", Type:=2) If DateIn Like "##/##/##" And IsDate(x) Then PropDate.Text = DateIn Else PropDate.Text = "??? That is not a date ???" End If Normally, this would be kind of an awkward interface as the TextBox is capable of receiving user typing directly. If you have an OK type CommandButton on the UserForm, I would let the user enter his/her date directly into the PropDate TextBox and put this code in that OK type CommandButton's Click event to verify the format of the entry... Private Sub CommandButton1_Click() With PropDate If Not (.Text Like "##/##/##" And IsDate(.Text)) Then MsgBox "Your entry is not a real date", vbCritical, "Bad Date Entry" .SelStart = 0 .SelLength = Len(.Text) .SetFocus Exit Sub End If End With ' ' The rest of your OK button code goes here ' End Sub There are other options available, but it all depends on what is on your UserForm and how the user is supposed to interact with whatever controls they are. -- Rick (MVP - Excel) "zak" wrote in message ... Hi Thanks for helping me out with this code. I actually have created a userform with a text input box (which is called PropDate). When the input box pops up and the date gets entered in the correct format - how can i add that date into the PropDate text input box on my userform? "Rick Rothstein" wrote: This is another possibility... Sub ordinate() Dim x As String x = Application.InputBox(prompt:="enter date mm/dd/yy", Type:=2) If x Like "##/##/##" And IsDate(x) Then MsgBox DateValue(x) Else MsgBox "bad input" End If End Sub -- Rick (MVP - Excel) "Gary''s Student" wrote in message ... Get the date as a String, and then detailed checks can be made: Sub ordinate() Dim x As String Dim n As Integer Dim dt As Date x = Application.InputBox(prompt:="enter date mm/dd/yy", Type:=2) If Len(x) < 8 Then MsgBox ("bad input") End If s = Split(x, "/") If UBound(s) < 2 Then MsgBox ("bad input") End If n = s(0) If n 12 Then MsgBox ("bad input") End If n = s(1) If n 31 Then MsgBox ("bad input") End If dt = DateValue(x) MsgBox (dt) End Sub -- Gary''s Student - gsnu200826 "zak" wrote: Hi I was wondering if anyone can let me know how to add validation to text input boxes on forms? I have several forms on a spreadsheet that I am creating and there are quite a few time and date input boxes, which I was hoping I could add validation to so that the information is entered in the correct format? i.e. date should only be in dd/mm/yy, time should only be entered like hh:mm etc. Can this be done? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User Form Text Boxes - Copy format of text boxes | Excel Discussion (Misc queries) | |||
Setting an array of text boxes equal to individual form text boxes | Excel Programming | |||
multi text boxes validation | Excel Programming | |||
validation for 10 text boxes | Excel Programming | |||
Two Quick Questions: Calling Subs & Text Input boxes | Excel Programming |