textbox validation with 3 conditions

[tkraju via OfficeKB.com]

Mr.Rick Rothstein gave me correct vb code for testing two textbox for
(equal)values.
How to codify the following logic with Greater than or less than parameter

I have 2 text boxes viz.TextBox1 and Textbox2 in a userform.TextBox1 has a
archival value from a worksheet source.
Condition1:The Textbox2 can be left blank without entering any
data/value.condition2:But if entry is needed only numeric values(numbers with
or without
decimals) be entered, condition3:and the TextBox2 value should not be greater
than TextBox1.
cursor should not move from the textbox until
correction/rectification done by user.
While rectification if user deletes whole entry and leave the textbox blank,
or
corrects the entry by putting exact values as in TextBox1.

 

[Bob Phillips]

With Me.TextBox2

If .Text <> "" And _
(Not IsNumeric(.Text) Or _
Val(.Text) > Me.TextBox1.Text) Then

MsgBox "Invalid value"
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
Exit Sub
End If

'OTHER CODE
End With


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[tkraju via OfficeKB.com]

thank you Bob.Working fine !
one more question.
how to validate date entries in TextBoxes (on Userform),user wants to enter
date values in dd/mm/yy form,after entry and exiting from textbox the textbox
entry be displayed in dd-mmm-yy format.

With Me.TextBox2

If .Text <> "" And _
(Not IsNumeric(.Text) Or _
Val(.Text) > Me.TextBox1.Text) Then

MsgBox "Invalid value"
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
Exit Sub
End If

'OTHER CODE
End With

Mr.Rick Rothstein gave me correct vb code for testing two textbox for
(equal)values.

[quoted text clipped - 15 lines]

or
corrects the entry by putting exact values as in TextBox1.

 

[Bob Phillips]

I would use the AfterUpdate event and just check that it is a date, let them
enter it however they want, and reform at it


Private Sub TextBox1_AfterUpdate()
With Me.TextBox1

If IsDate(.Text) Then

.Text = Format(CDate(.Text), "dd-mmm-yy")
Else

MsgBox "Invalid date entry"
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
End If
End With
End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

thank you Bob.Working fine !
one more question.
how to validate date entries in TextBoxes (on Userform),user wants to
enter
date values in dd/mm/yy form,after entry and exiting from textbox the
textbox
entry be displayed in dd-mmm-yy format.

With Me.TextBox2

If .Text <> "" And _
(Not IsNumeric(.Text) Or _
Val(.Text) > Me.TextBox1.Text) Then

MsgBox "Invalid value"
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
Exit Sub
End If

'OTHER CODE
End With

Mr.Rick Rothstein gave me correct vb code for testing two textbox for
(equal)values.

[quoted text clipped - 15 lines]

or
corrects the entry by putting exact values as in TextBox1.

 

[tkraju via OfficeKB.com]

Thank you Bob,Please ignore my reply in other thread.Thanks once again.

I would use the AfterUpdate event and just check that it is a date, let them
enter it however they want, and reform at it

Private Sub TextBox1_AfterUpdate()
With Me.TextBox1

If IsDate(.Text) Then

.Text = Format(CDate(.Text), "dd-mmm-yy")
Else

MsgBox "Invalid date entry"
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
End If
End With
End Sub

thank you Bob.Working fine !
one more question.

[quoted text clipped - 25 lines]

 

[tkraju via OfficeKB.com]

Mr.Bob, thank you but this code has not given me desired results.I entered in
Textbox1 a date as
04/06/08(means 4th June 2008),it converted to 06-Apr-08(6thApril2008).
what is the remedy?

I would use the AfterUpdate event and just check that it is a date, let them
enter it however they want, and reform at it

Private Sub TextBox1_AfterUpdate()
With Me.TextBox1

If IsDate(.Text) Then

.Text = Format(CDate(.Text), "dd-mmm-yy")
Else

MsgBox "Invalid date entry"
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
End If
End With
End Sub

thank you Bob.Working fine !
one more question.

[quoted text clipped - 25 lines]

 

[tkraju via OfficeKB.com]

Mr.Bob, thank you but this code has not given me desired results.I entered in
Textbox1 a date as
04/06/08(means 4th June 2008),it converted to 06-Apr-08(6thApril2008).
what is the remedy?

I would use the AfterUpdate event and just check that it is a date, let them
enter it however they want, and reform at it

Private Sub TextBox1_AfterUpdate()
With Me.TextBox1

If IsDate(.Text) Then

.Text = Format(CDate(.Text), "dd-mmm-yy")
Else

MsgBox "Invalid date entry"
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
End If
End With
End Sub

thank you Bob.Working fine !
one more question.

[quoted text clipped - 25 lines]

 

[TUNGANA KURMA RAJU]

Mr.Bob,Your code hasn't given me desired results.I entered a date value in
TextBox1, 04/06/08(what I mean 4thJune2008),it converted to 06-Apr-08.I want
result 04-Jun-08.

I would use the AfterUpdate event and just check that it is a date, let them
enter it however they want, and reform at it


Private Sub TextBox1_AfterUpdate()
With Me.TextBox1

If IsDate(.Text) Then

.Text = Format(CDate(.Text), "dd-mmm-yy")
Else

MsgBox "Invalid date entry"
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
End If
End With
End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

thank you Bob.Working fine !
one more question.
how to validate date entries in TextBoxes (on Userform),user wants to
enter
date values in dd/mm/yy form,after entry and exiting from textbox the
textbox
entry be displayed in dd-mmm-yy format.

With Me.TextBox2

If .Text <> "" And _
(Not IsNumeric(.Text) Or _
Val(.Text) > Me.TextBox1.Text) Then

MsgBox "Invalid value"
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
Exit Sub
End If

'OTHER CODE
End With

Mr.Rick Rothstein gave me correct vb code for testing two textbox for
(equal)values.
[quoted text clipped - 15 lines]
or
corrects the entry by putting exact values as in TextBox1.

 

[Norman Jones]

Hi Tungana,

Bob's code worked for me.

I could reproduce your result if I employed
US date settings on my system and I entered
the date as 04/06/08.

However, to enter the date 4th June 2008,
a US user would type 6/04/08 and a
European user would type 4/6/08.

In either case, Bob's code would provide
the the required (June) date in dd-mmm-yy
format, as requested.



---
Regards.
Norman

Mr.Bob,Your code hasn't given me desired results.I entered a date value in
TextBox1, 04/06/08(what I mean 4thJune2008),it converted to 06-Apr-08.I
want
result 04-Jun-08.

I would use the AfterUpdate event and just check that it is a date, let
them
enter it however they want, and reform at it


Private Sub TextBox1_AfterUpdate()
With Me.TextBox1

If IsDate(.Text) Then

.Text = Format(CDate(.Text), "dd-mmm-yy")
Else

MsgBox "Invalid date entry"
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
End If
End With
End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

thank you Bob.Working fine !
one more question.
how to validate date entries in TextBoxes (on Userform),user wants to
enter
date values in dd/mm/yy form,after entry and exiting from textbox the
textbox
entry be displayed in dd-mmm-yy format.

Bob Phillips wrote:
With Me.TextBox2

If .Text <> "" And _
(Not IsNumeric(.Text) Or _
Val(.Text) > Me.TextBox1.Text) Then

MsgBox "Invalid value"
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
Exit Sub
End If

'OTHER CODE
End With

Mr.Rick Rothstein gave me correct vb code for testing two textbox
for
(equal)values.
[quoted text clipped - 15 lines]
or
corrects the entry by putting exact values as in TextBox1.

 

[TUNGANA KURMA RAJU]

My system also has Us date settings,but why the code has not given me the
desired results?

Hi Tungana,

Bob's code worked for me.

I could reproduce your result if I employed
US date settings on my system and I entered
the date as 04/06/08.

However, to enter the date 4th June 2008,
a US user would type 6/04/08 and a
European user would type 4/6/08.

In either case, Bob's code would provide
the the required (June) date in dd-mmm-yy
format, as requested.



---
Regards.
Norman

Mr.Bob,Your code hasn't given me desired results.I entered a date value in
TextBox1, 04/06/08(what I mean 4thJune2008),it converted to 06-Apr-08.I
want
result 04-Jun-08.

I would use the AfterUpdate event and just check that it is a date, let
them
enter it however they want, and reform at it


Private Sub TextBox1_AfterUpdate()
With Me.TextBox1

If IsDate(.Text) Then

.Text = Format(CDate(.Text), "dd-mmm-yy")
Else

MsgBox "Invalid date entry"
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
End If
End With
End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

thank you Bob.Working fine !
one more question.
how to validate date entries in TextBoxes (on Userform),user wants to
enter
date values in dd/mm/yy form,after entry and exiting from textbox the
textbox
entry be displayed in dd-mmm-yy format.

Bob Phillips wrote:
With Me.TextBox2

If .Text <> "" And _
(Not IsNumeric(.Text) Or _
Val(.Text) > Me.TextBox1.Text) Then

MsgBox "Invalid value"
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
Exit Sub
End If

'OTHER CODE
End With

Mr.Rick Rothstein gave me correct vb code for testing two textbox
for
(equal)values.
[quoted text clipped - 15 lines]
or
corrects the entry by putting exact values as in TextBox1.

 

[Norman Jones]

Hi Tungana,

If you employ US settings, an entry of 04/06/08
will be interpreted as 6 April, 2008; using US
settings, dates will be read as US dates.

To return the a date of 4 June, 2008, either
change your system settings to read European
dates, or (from a US perspective) correctly
enter the date as mm/dd/yy, i.e: 06/04/08.

To repeat, a user with US settings and a user
with European settings will both obtain the
requisite June date provided that the each enters
the date in the form expected by their system
settings.



---
Regards.
Norman

My system also has Us date settings,but why the code has not given me the
desired results?

Hi Tungana,

Bob's code worked for me.

I could reproduce your result if I employed
US date settings on my system and I entered
the date as 04/06/08.

However, to enter the date 4th June 2008,
a US user would type 6/04/08 and a
European user would type 4/6/08.

In either case, Bob's code would provide
the the required (June) date in dd-mmm-yy
format, as requested.



---
Regards.
Norman


in
message news:[email protected]...

Mr.Bob,Your code hasn't given me desired results.I entered a date value
in
TextBox1, 04/06/08(what I mean 4thJune2008),it converted to 06-Apr-08.I
want
result 04-Jun-08.

:

I would use the AfterUpdate event and just check that it is a date,
let
them
enter it however they want, and reform at it


Private Sub TextBox1_AfterUpdate()
With Me.TextBox1

If IsDate(.Text) Then

.Text = Format(CDate(.Text), "dd-mmm-yy")
Else

MsgBox "Invalid date entry"
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
End If
End With
End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

thank you Bob.Working fine !
one more question.
how to validate date entries in TextBoxes (on Userform),user wants
to
enter
date values in dd/mm/yy form,after entry and exiting from textbox
the
textbox
entry be displayed in dd-mmm-yy format.

Bob Phillips wrote:
With Me.TextBox2

If .Text <> "" And _
(Not IsNumeric(.Text) Or _
Val(.Text) > Me.TextBox1.Text) Then

MsgBox "Invalid value"
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
Exit Sub
End If

'OTHER CODE
End With

Mr.Rick Rothstein gave me correct vb code for testing two textbox
for
(equal)values.
[quoted text clipped - 15 lines]
or
corrects the entry by putting exact values as in TextBox1.