Data Validation for % input in userfom

[amit]

hi - I have a user entry text box to enter a % value...and a submit command
button which mutiplies the entry with another number. i'm able to take inputs
both as % and as whole numbers and based on the input..either remove the % or
if whole number then divide by 100. i would like to put a change event which
catches any input other than numers or %....valid entries would be 100% or
100.....need help with being able to catch entries like rree or rr%

any help much appreciated.

regards,
Amit

 

[Gary''s Student]

We can explicitly test the input:

Sub mesage()
x = Application.InputBox(Prompt:="Enter value:", Type:=2)
ok = False
If IsNumeric(x) Then
ok = True
Else
l = Len(x)
If l > 1 Then
v1 = Right(x, 1)
v2 = Left(x, l - 1)
If v1 = "%" And IsNumeric(v2) Then
ok = True
End If
End If
End If

If ok Then
MsgBox ("data good")
Else
MsgBox ("data not valid")
End If
End Sub

We test if the data is a number or a number followed by the % character.

 

[Rick Rothstein \(MVP - VB\)]

For your assignment to v2, I think you meant

v2 = Left(x, Len(x) - 1)

Rick

 

[Rick Rothstein \(MVP - VB\)]

I just noticed... you defined the lower case "L" as being equal to Len(x).
That is a bad choice for a variable name... on my system, it looked like
your wrote One minus One rather than El minus One, hence my comment.

Rick

 

[Rob Bovey]

Hi Amit,

You can use the KeyPress event of the TextBox to check each character as
it's entered and either accept it or reject it. In the example below,
numbers, the % symbol and backspace are allowed, while all other characters
are cancelled.

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 8, 37, 48 To 57
''' Backspace, % and numbers are all OK
Case Else
''' Cancel all other characters.
KeyAscii = 0
End Select
End Sub

Even with this event in place you'll still need to do some additional
validation in your Submit button click event to trap for the few situations
where the user can still enter invalid data, like 12%3 or just %.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[Gary''s Student]

I agree.
--
Gary''s Student - gsnu200785

I just noticed... you defined the lower case "L" as being equal to Len(x).
That is a bad choice for a variable name... on my system, it looked like
your wrote One minus One rather than El minus One, hence my comment.

Rick

 

[Rick Rothstein \(MVP - VB\)]

Just figured I would mention that users will still be able to paste in
invalid data, so a more thorough check of the entry will still be necessary.
This type of problem (restrict entry to certain characters) came up many
times over in the compiled VB newsgroups where I originally frequented a
lot. Here is a modification of the routine I used to post which handles
digits only (no decimal point, although a routine for that can be created if
needed) and an optional single trailing percent sign and which will not let
a user paste bad data in. All the following code should be copy/pasted
exactly as is into the UserForm code window.

'******************** START OF CODE ********************
Dim LastPosition As Long

Private Sub TextBox1_Change()
Static LastText As String
Static SecondTime As Boolean
If Not SecondTime Then
With TextBox1
If .Text Like "*[!0-9%]*" Or .Text Like "*%?" Or _
.Text Like "*%*%*" Or .Text = "%" Then
Beep
SecondTime = True
.Text = LastText
.SelStart = LastPosition
Else
LastText = .Text
End If
End With
End If
SecondTime = False
End Sub

Private Sub TextBox1_MouseDown(ByVal Button As Integer, ByVal Shift As _
Integer, ByVal X As Single, ByVal Y As Single)
With TextBox1
LastPosition = .SelStart
'Place any other MouseDown event code here
End With
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
With TextBox1
LastPosition = .SelStart
'Place any other KeyPress checking code here
End With
End Sub
'******************** END OF CODE ********************

Rick

 

[Rob Bovey]

Hi Rick,

Good point, I'd never noticed the paste problem. My KeyPress validation
has been probably been bypassed like this plenty of times, but since I
almost always add a second layer of validation, I just never found out about
it.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

Just figured I would mention that users will still be able to paste in
invalid data, so a more thorough check of the entry will still be
necessary. This type of problem (restrict entry to certain characters)
came up many times over in the compiled VB newsgroups where I originally
frequented a lot. Here is a modification of the routine I used to post
which handles digits only (no decimal point, although a routine for that
can be created if needed) and an optional single trailing percent sign and
which will not let a user paste bad data in. All the following code should
be copy/pasted exactly as is into the UserForm code window.

'******************** START OF CODE ********************
Dim LastPosition As Long

Private Sub TextBox1_Change()
Static LastText As String
Static SecondTime As Boolean
If Not SecondTime Then
With TextBox1
If .Text Like "*[!0-9%]*" Or .Text Like "*%?" Or _
.Text Like "*%*%*" Or .Text = "%" Then
Beep
SecondTime = True
.Text = LastText
.SelStart = LastPosition
Else
LastText = .Text
End If
End With
End If
SecondTime = False
End Sub

Private Sub TextBox1_MouseDown(ByVal Button As Integer, ByVal Shift As _
Integer, ByVal X As Single, ByVal Y As Single)
With TextBox1
LastPosition = .SelStart
'Place any other MouseDown event code here
End With
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
With TextBox1
LastPosition = .SelStart
'Place any other KeyPress checking code here
End With
End Sub
'******************** END OF CODE ********************

Rick

Hi Amit,

You can use the KeyPress event of the TextBox to check each character
as it's entered and either accept it or reject it. In the example below,
numbers, the % symbol and backspace are allowed, while all other
characters are cancelled.

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 8, 37, 48 To 57
''' Backspace, % and numbers are all OK
Case Else
''' Cancel all other characters.
KeyAscii = 0
End Select
End Sub

Even with this event in place you'll still need to do some additional
validation in your Submit button click event to trap for the few
situations where the user can still enter invalid data, like 12%3 or just
%.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[amit]

Thx a lot Gary, Rob and Rick...all very good suggestions.....i've bot the
methods u've suggested and i do have a second level of validation to
accomadate 2%3....

thx guys for all your responses..much appreciated...

Hi Rick,

Good point, I'd never noticed the paste problem. My KeyPress validation
has been probably been bypassed like this plenty of times, but since I
almost always add a second layer of validation, I just never found out about
it.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

Just figured I would mention that users will still be able to paste in
invalid data, so a more thorough check of the entry will still be
necessary. This type of problem (restrict entry to certain characters)
came up many times over in the compiled VB newsgroups where I originally
frequented a lot. Here is a modification of the routine I used to post
which handles digits only (no decimal point, although a routine for that
can be created if needed) and an optional single trailing percent sign and
which will not let a user paste bad data in. All the following code should
be copy/pasted exactly as is into the UserForm code window.

'******************** START OF CODE ********************
Dim LastPosition As Long

Private Sub TextBox1_Change()
Static LastText As String
Static SecondTime As Boolean
If Not SecondTime Then
With TextBox1
If .Text Like "*[!0-9%]*" Or .Text Like "*%?" Or _
.Text Like "*%*%*" Or .Text = "%" Then
Beep
SecondTime = True
.Text = LastText
.SelStart = LastPosition
Else
LastText = .Text
End If
End With
End If
SecondTime = False
End Sub

Private Sub TextBox1_MouseDown(ByVal Button As Integer, ByVal Shift As _
Integer, ByVal X As Single, ByVal Y As Single)
With TextBox1
LastPosition = .SelStart
'Place any other MouseDown event code here
End With
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
With TextBox1
LastPosition = .SelStart
'Place any other KeyPress checking code here
End With
End Sub
'******************** END OF CODE ********************

Rick

Hi Amit,

You can use the KeyPress event of the TextBox to check each character
as it's entered and either accept it or reject it. In the example below,
numbers, the % symbol and backspace are allowed, while all other
characters are cancelled.

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 8, 37, 48 To 57
''' Backspace, % and numbers are all OK
Case Else
''' Cancel all other characters.
KeyAscii = 0
End Select
End Sub

Even with this event in place you'll still need to do some additional
validation in your Submit button click event to trap for the few
situations where the user can still enter invalid data, like 12%3 or just
%.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

hi - I have a user entry text box to enter a % value...and a submit
command
button which mutiplies the entry with another number. i'm able to take
inputs
both as % and as whole numbers and based on the input..either remove the
% or
if whole number then divide by 100. i would like to put a change event
which
catches any input other than numers or %....valid entries would be 100%
or
100.....need help with being able to catch entries like rree or rr%

any help much appreciated.

regards,
Amit

 

[Rick Rothstein \(MVP - VB\)]

I'd be interested in seeing your 2nd level of validation code.

Rick

Thx a lot Gary, Rob and Rick...all very good suggestions.....i've bot the
methods u've suggested and i do have a second level of validation to
accomadate 2%3....

thx guys for all your responses..much appreciated...

Hi Rick,

Good point, I'd never noticed the paste problem. My KeyPress
validation
has been probably been bypassed like this plenty of times, but since I
almost always add a second layer of validation, I just never found out
about
it.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

in
message news:[email protected]...

Just figured I would mention that users will still be able to paste in
invalid data, so a more thorough check of the entry will still be
necessary. This type of problem (restrict entry to certain characters)
came up many times over in the compiled VB newsgroups where I
originally
frequented a lot. Here is a modification of the routine I used to post
which handles digits only (no decimal point, although a routine for
that
can be created if needed) and an optional single trailing percent sign
and
which will not let a user paste bad data in. All the following code
should
be copy/pasted exactly as is into the UserForm code window.

'******************** START OF CODE ********************
Dim LastPosition As Long

Private Sub TextBox1_Change()
Static LastText As String
Static SecondTime As Boolean
If Not SecondTime Then
With TextBox1
If .Text Like "*[!0-9%]*" Or .Text Like "*%?" Or _
.Text Like "*%*%*" Or .Text = "%" Then
Beep
SecondTime = True
.Text = LastText
.SelStart = LastPosition
Else
LastText = .Text
End If
End With
End If
SecondTime = False
End Sub

Private Sub TextBox1_MouseDown(ByVal Button As Integer, ByVal Shift As
_
Integer, ByVal X As Single, ByVal Y As Single)
With TextBox1
LastPosition = .SelStart
'Place any other MouseDown event code here
End With
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
With TextBox1
LastPosition = .SelStart
'Place any other KeyPress checking code here
End With
End Sub
'******************** END OF CODE ********************

Rick


Hi Amit,

You can use the KeyPress event of the TextBox to check each
character
as it's entered and either accept it or reject it. In the example
below,
numbers, the % symbol and backspace are allowed, while all other
characters are cancelled.

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 8, 37, 48 To 57
''' Backspace, % and numbers are all OK
Case Else
''' Cancel all other characters.
KeyAscii = 0
End Select
End Sub

Even with this event in place you'll still need to do some
additional
validation in your Submit button click event to trap for the few
situations where the user can still enter invalid data, like 12%3 or
just
%.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

hi - I have a user entry text box to enter a % value...and a submit
command
button which mutiplies the entry with another number. i'm able to
take
inputs
both as % and as whole numbers and based on the input..either remove
the
% or
if whole number then divide by 100. i would like to put a change
event
which
catches any input other than numers or %....valid entries would be
100%
or
100.....need help with being able to catch entries like rree or rr%

any help much appreciated.

regards,
Amit