prevent multiple decimal points during textbox input
2

Question

prevent multiple decimal points during textbox input

Tobin1 (Petroleum)

(OP)

9 Jun 08 19:32

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
www.nov.com

FrancisL (Computer) · Answer 1 · 2008-06-10 04:27:23

RE: prevent multiple decimal points during textbox input

FrancisL (Computer) 10 Jun 08 04:27

Look up MaskedEdit control

handleman (Automotive) · Answer 2 · 2008-06-10 06:30:57

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.

CODE

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 (Mechanical) · Answer 3 · 2008-06-10 07:27:13

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

Tobin1 (Petroleum) · Answer 4 · 2008-06-10 07:43:02

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
www.nov.com

handleman (Automotive) · Answer 5 · 2008-06-10 07:52:40

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 (Petroleum) · Answer 6 · 2008-06-10 07:57:04

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
www.nov.com

TheTick (Mechanical) · Answer 7 · 2008-06-10 09:28:53

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:

Use KeyDown, KeyUp, or KeyPress events to detect when Tab or Enter keys are pressed.
Use textbox's Enter & Exit events which are triggered when the object gets/loses focus.

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

CODE

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

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

Tobin1 (Petroleum) · Answer 8 · 2008-06-10 10:01:26

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
www.nov.com

handleman (Automotive) · Answer 9 · 2008-06-10 10:29:16

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 (Petroleum) · Answer 10 · 2008-06-10 10:41:44

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
www.nov.com

cowski (Mechanical) · Answer 11 · 2008-06-10 16:05:09

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 (Petroleum) · Answer 12 · 2008-06-10 16:21:39

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
www.nov.com

TheTick (Mechanical) · Answer 13 · 2008-06-10 16:30:49

RE: prevent multiple decimal points during textbox input

TheTick (Mechanical) 10 Jun 08 16:30

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

Tobin1 (Petroleum) · Answer 14 · 2008-06-10 16:34:55

TheTick,

OOHH! - They hide it :-) .

Thanks

Tobin Sparks
www.nov.com

handleman (Automotive) · Answer 15 · 2008-06-10 20:01:12

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 (Petroleum) · Answer 16 · 2008-06-11 08:04:33

handleman,

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

Thanks

Tobin Sparks
www.nov.com

cummings54 (Chemical) · Answer 17 · 2008-06-11 10:02:38

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

CODE

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. bigsmile

Tobin1 (Petroleum) · Answer 18 · 2008-06-11 10:24:22

cowski,

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

Thanks

Tobin Sparks
www.nov.com

Tobin1 (Petroleum) · Answer 19 · 2008-06-11 11:28:36

cummings54,

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

.

Thanks Again

Tobin Sparks
www.nov.com

handleman (Automotive) · Answer 20 · 2008-06-11 11:38:16

Cummings,

What happens if the user enters 15.51.54?

-handleman, CSWP (The new, easy test)

Tobin1 (Petroleum) · Answer 21 · 2008-06-11 11:55:47

handleman,

I'll still catch the erroneous input when the code validates the input after the user pulls the trigger.

I'm still open to a little electrical shock :-) .

Thanks

Tobin Sparks
www.nov.com

Come Join Us!

Posting Guidelines

prevent multiple decimal points during textbox input
2