Validation on text input boxes on a form

[zak]

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?

 

[Gary''s Student]

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

 

[Rick Rothstein]

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 Rothstein]

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

 

[JLGWhiz]

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]

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.

 

[Harald Staff]

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]

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.

 

[Harald Staff]

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

 

[Gary''s Student]

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.

 

[zak]

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]

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.