Validating textbox inputs

[Shea]

How do I ensure that a textbox on a userform only accepts a positive numeric
value (or remains empty)?

[Harald Staff]

Hi

Assign this code to it:

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii = 44 Then Exit Sub
'delete previous this if decimal comma is unacceptable
If KeyAscii = 46 Then Exit Sub
'delete previous this if decimal point is unacceptable
If KeyAscii > 57 Then KeyAscii = 0
If KeyAscii < 48 Then KeyAscii = 0
End Sub

Best wishes Harald

Shea <ju...@geller.screaming.net> skrev i
news:978517693.28887.0...@news.demon.co.uk...

[Shea]

This is very good but it will still allow multiple decimal points. It also
is 'messy'
(no insult intended). what I was looking for was a method of testing the
input so that an appropriate error message could could be generated and the
focus returned to that textbox (prefrably with the whole text selected). any
help much appreciated as always.
"Harald Staff" <harald...@nrk.no> wrote in message
news:uEkSV2XdAHA.2140@tkmsftngp02...

[Harald Staff]

Ok. One decimal point:

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

If KeyAscii = 46 Then
If InStr(TextBox1.Text, ".") Then KeyAscii = 0
Exit Sub
End If

If KeyAscii > 57 Then KeyAscii = 0
If KeyAscii < 48 Then KeyAscii = 0
End Sub

Messy ? You want clean code but have the user to complete an illegal entry
before prompting her to re-enter ? I believe we disagree a little here. Ok,
I'll provide a "just-a-little-annoying" solution; place a label to the right
of the textbox, no caption text, and try this:

Private Sub TextBox1_Change()
Label1.Caption = ""
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

If KeyAscii = 46 Then
Dim i As Integer
If InStr(TextBox1.Text, ".") Then
GoTo NoGood
Else
Exit Sub
End If
End If
If KeyAscii > 57 Then GoTo NoGood
If KeyAscii < 48 Then GoTo NoGood
Exit Sub
NoGood:
KeyAscii = 0
Label1.Caption = "Positive numeric entry only"
End Sub

Best wishes Harald

Shea <ju...@geller.screaming.net> skrev i
news:978525297.1956.0...@news.demon.co.uk...

[Shea]

This code is much better thankyou. Wht I was realy after was the following:-
(own code after trial and error): -

Private Sub txtXBandwidth_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With txtXBandwidth
If IsNumeric(.Text) = False Then
.SelStart = 0
.SelLength = Len(.Text)
MsgBox "Please enter a positve number", vbCritical, "Bad entry"
Cancel = True
ElseIf .Text <= 0 Then
.SelStart = 0
.SelLength = Len(.Text)
MsgBox "Please enter a positve number", vbCritical, "Bad entry"
Cancel = True
End If
End With
End Sub

Any improvement welcome! I'm just learning VBA this is my 4th week!

"Harald Staff" <harald...@nrk.no> wrote in message

news:u3YqfEZdAHA.708@tkmsftngp05...

[Shea]

Sorry for double posting, but my pevious post was a little ambiguous what it
should have said is: -

The new code was much better, thankyou.

What I was realy after was the following:-

(own code after trial and error): -

Private Sub txtXBandwidth_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With txtXBandwidth
If IsNumeric(.Text) = False Then
.SelStart = 0
.SelLength = Len(.Text)
MsgBox "Please enter a positve number", vbCritical, "Bad entry"
Cancel = True
ElseIf .Text <= 0 Then
.SelStart = 0
.SelLength = Len(.Text)
MsgBox "Please enter a positve number", vbCritical, "Bad entry"
Cancel = True
End If
End With
End Sub

Any improvement welcome! I'm just learning VBA this is my 4th week!

"Harald Staff" <harald...@nrk.no> wrote in message

news:u3YqfEZdAHA.708@tkmsftngp05...

[Ron de Bruin]

If you use 2000 then change the msgbox to a label display.(see Haralds post)
In 2000 you lost your focus on the textbox and you must click in the textbox
to try another input.

Regards Ron

"Shea" <ju...@geller.screaming.net> schreef in bericht
news:978533535.24547.0...@news.demon.co.uk...

[Harald Staff]

Hi

For code learning purposes your programming is good. But you do have quite a
few things to learn about user interface design. It should be obvious that
that box is for that kind of entries, errors should fix themselves, and
messages should not be given in a way that the user is forced to go "OK"
before continuing -she is in the middle of some keyboard work and will loose
keystrokes that way. Try to annoy people as little as possible -this just as
a friendly advice.

Best wishes Harald