Validating Textbox-Value
 

Hi newsgroup

In my excel form I have a textbox where a date value can be entered. When I
enter a wrong value excel crashes.

Now I like to validate this entry with some kind of entry mask. I like to
verify that the entered value cannot be someting other than a date?

Is there a possibility to do this in excel?

Thanks alot

Ueli Werner

Validating Textbox-Value
 

Use ISDATE() which return True if valid date

If this post helps click Yes
---------------
Jacob Skaria


"Ueli Werner" wrote:

Hi newsgroup

In my excel form I have a textbox where a date value can be entered. When I
enter a wrong value excel crashes.

Now I like to validate this entry with some kind of entry mask. I like to
verify that the entered value cannot be someting other than a date?

Is there a possibility to do this in excel?

Thanks alot

Ueli Werner

Validating Textbox-Value
 

First of all, what sort of textbox is this? One from the Forms
commandbar or one from the Controls commandbar. Or is it on a UserForm
in VBA? When you say "excel crashes", what do you really mean. Does
the entire application crash (with a message like "Excel has
encountered and error and needs to be restarted") or by "crash" do you
mean a run time error (e.g., "Type Mismatch") that can be handled with
the appropriate error handling code? Finally, what date format is
valid? In what manner do you want to restrict user input?

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Fri, 17 Apr 2009 13:13:49 +0200, "Ueli Werner"
wrote:

Hi newsgroup

In my excel form I have a textbox where a date value can be entered. When I
enter a wrong value excel crashes.

Now I like to validate this entry with some kind of entry mask. I like to
verify that the entered value cannot be someting other than a date?

Is there a possibility to do this in excel?

Thanks alot

Ueli Werner

Validating Textbox-Value
 

If IsDate(TextBox1.Text) = False Then
MsgBox "Enter a valid Date in format MM/DD/YYYY"
Exit Sub
End If
--
If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Use ISDATE() which return True if valid date

If this post helps click Yes
---------------
Jacob Skaria


"Ueli Werner" wrote:

Hi newsgroup

In my excel form I have a textbox where a date value can be entered. When I
enter a wrong value excel crashes.

Now I like to validate this entry with some kind of entry mask. I like to
verify that the entered value cannot be someting other than a date?

Is there a possibility to do this in excel?

Thanks alot

Ueli Werner

Validating Textbox-Value
 

How about this? It seems to work for me.

Private Sub txtStartDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)

On Error GoTo ErrThisSub

If Len(Me.txtStartDate) 0 Then
Me.txtStartDate = DateValue(FormatDateTime(Me.txtStartDate, vbShortDate))
End If

Exit Sub

ErrThisSub:
MsgBox "Unexpected error formatting Start Date entry." & vbCrLf & _
"Error: " & Err.Number & "-" & Err.Description
Me.txtStartDate.Value = ""
Cancel = True
Resume Next
End Sub

Where txtStartDate is the name of my textbox control on my form. The
FormatDateTime function takes just about any string that could be formatted
to a date and returns a valid date type. Even the strings 39931 becomes
4/27/2009.

John

"Ueli Werner" wrote:

Hi newsgroup

In my excel form I have a textbox where a date value can be entered. When I
enter a wrong value excel crashes.

Now I like to validate this entry with some kind of entry mask. I like to
verify that the entered value cannot be someting other than a date?

Is there a possibility to do this in excel?

Thanks alot

Ueli Werner