prevent multiple decimal points during textbox input 2

[Tobin1]

Howdy All,

I'm looking for a good way to detect if the user has entered more than one decimal point into a TextBox. I'm trying to prevent a error due to multiple decimal points.

This seems like it should be easy and there's probably a bunch of ways to do this, but I've been looking and can't seem to find even one.

I was thinking of trying to get the decimal point (.) count in a string.

Can anyone help?

Thanks

Tobin Sparks

http://www.nov.com

 

[FrancisL]

Look up MaskedEdit control

 

[handleman]

You can test with a single line of code. Use the Split function with a "." delimiter, then use UBound to find how many strings were returned. If more than one decimal point is found, the array will have more than 2 elements. Since UBound is zero-based, compare the result to 1.

If UBound(Split(myTextBox.Value, ".", -1, vbTextCompare)) > 1 Then
    MsgBox "Too many decimals!"
Else
    MsgBox "Input OK!"
End If

-handleman, CSWP (The new, easy test)

 

[TheTick]

Use the "Validation" event on your control to double-check inputs.

 

[Tobin1]

Howdy,

Thank you ALL. This seems so simple ans basic it should be easier to find. I'll check it out later today.

Thanks Again

Tobin Sparks

http://www.nov.com

 

[handleman]

Tick,
I don't see the Validation event available for a TextBox control in VBA. Is it only in VB6/.net, or am I just looking in the wrong spot?

-handleman, CSWP (The new, easy test)

 

[Tobin1]

handleman,

I was thinking the same thing, but, I did't get to look again just to make sure. I'm currently using a Keyup (or down) event for input. I could use a change event also.

Thanks

Tobin Sparks

http://www.nov.com

 

[TheTick]

Sorry for the less-than-useful non-advice. Validation events are not present in VBA.

When trying to make a user-proof app, you can easily spend more than half your programming time on grooming user input.

Other ways to trigger validation:
[ul][li]Use KeyDown, KeyUp, or KeyPress events to detect when Tab or Enter keys are pressed.[/li]
[li]Use textbox's Enter & Exit events which are triggered when the object gets/loses focus.[/li][/ul]

A quick way to validate is to just assign the textbox contents to a variable and return it to the textbox.

Textbox1.Text = CStr(CDbl(Textbox1.Text))

Use "On Error" statement to direct validation to error handling.

 

[Tobin1]

handleman, FrancisL and TheTick,

Thanks for your efforts in answering my question.

handleman - Thanks for the above code. It works perfect in a Change event. The text entry event I'm using is Keypress .
I have to tell you though, I'm not familiar with "Split function" so I'm only saying that it works but I can't say why .
I'd have never come up with it myself or even thought to search for it that way.

Thanks Again

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

Dim ch As String

' Allow only numbers and "." to be entered in the box
ch = Chr$(KeyAscii)
If Not (ch >= "0" And ch <= "9" Or ch = ".") Then KeyAscii = 0

End Sub


Private Sub QTBallTxtSpace_Change()

' Limit the number of decimal points to avoid an error
If UBound(Split(QTBallTxtSpace.Value, ".", -1, vbTextCompare)) > 1 Then MsgBox "Too many decimals points "

End Sub

Tobin Sparks

http://www.nov.com

 

[handleman]

A quick warning: putting code in the KeyPress event is not sufficient to prevent the user from inputting non-numerical data. Ctrl-V doesn't fire the KeyPress event.

-handleman, CSWP (The new, easy test)

 

[Tobin1]

handleman,

Well - that's good to know.
Do you know how to send an electrical shock to the keyboard? Not - high voltage or anything - just a little shock.
Just enough to change behavior .

Thanks Again


Tobin Sparks

http://www.nov.com

 

[cowski]

Try out the 'IsNumeric()' function. I would suggest using 'IsNumeric()' in the lost focus event and if the input is not numeric, pop up a message and give the focus back to the textbox.

 

[Tobin1]

cowski

Thanks for your response. Apparently a "lost focus" event is not available in SW API for a TextBox.

It appears the best way to disable Ctrl+V is to disable this Windows function at the start up of the form, then enable at exit.

Not real happy with that though.

Thanks Again


Tobin Sparks

http://www.nov.com

 

[TheTick]

"Exit" ~= "Lost Focus" for TextBox.

 

[Tobin1]

TheTick,

OOHH! - They hide it .

Thanks

Tobin Sparks

http://www.nov.com

 

[handleman]

You'll never be able to totally prevent any user from possibly getting invalid text into that box. Instead of trying, do your final validation in code immediately before it is actually used in your program.

-handleman, CSWP (The new, easy test)

 

[Tobin1]

handleman,

Sounds like good advice, I'll do what I can.

Thanks

Tobin Sparks

http://www.nov.com

 

[cummings54]

Here's a little snippet that makes use of the keypress event

Dim LastKeyAscii As Variant
Dim LastTextBox1 As String

Private Sub TextBox1_GotFocus()
LastKeyAscii = 0
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii = 46 And (LastKeyAscii = KeyAscii) Then
Beep
TextBox1.Text = LastTextBox1
End If
LastKeyAscii = KeyAscii
LastTextBox1 = TextBox1.Text
End Sub

When two decimal points are entered you get a beep and the orignal text is restored. To the user it looks as if nothing happens if a second consequtive decimal point is attempted.

For training purposes OSHA may find a beep more acceptable than a tiny electric shock.

 

[Tobin1]

cowski,

Thanks for the 'IsNumeric()' clue. I found a good way to use it to validate input. Very helpful.

Thanks

Tobin Sparks

http://www.nov.com

 

[Tobin1]

cummings54,

That's very interesting. I really appreciate your input.
Sometimes OSHA can spoil my fun .

Thanks Again

Tobin Sparks

http://www.nov.com