textbox: only number can enter how

in the textbox, only number or number with decimal
can allow when user enter the value, how the code look like

 

Try this
Source: http://www.vbcad.com/showatip.asp?TIPID=1

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If InStr(1, TextBox1.Text, ".") > 0 And KeyAscii = Asc(".") Then
KeyAscii = 0
Exit Sub
End If
Select Case KeyAscii
Case Asc("0") To Asc("9"), Asc(".")
Case Else
KeyAscii = 0
End Select
End Sub

--
Regards
Tom Roberts
__________________________
MechWest Design & Drafting
Perth, Western Australia

 

thanks Tom

 

when i copy the code to others (textbox2) and i change the textbox name,
when i run only textbox1 can put value with decimal others textbox can't put
point (.) why???

 

Take a look at the VB(A) IsNumeric function. Try it in the BeforeUpdate
event for the textbox.

 

in the textbox, only number or number with decimal

Depends upon how much control you want over what they type. Both solutions
already posted will work but suppose you want to limit the user to only
typing in numbers to the nearest hundredth [*.XX] - neither solution
posted can accommodate that. If you want more control than use a regular
expression. In your project, add a reference to Microsoft VBScript Regular
Expressions 5.5. Then use this code:

Private oReg As RegExp
Private Const DefaultNumber = "0.00"

Private Sub UserForm_Initialize()
Set oReg = New RegExp
With oReg
.IgnoreCase = True
.Pattern = "^\d*.\d{2}$"
End With
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Not oReg.Test(TextBox1) Then TextBox1 = DefaultNumber
End Sub


-- Mike
___________________________
Mike Tuersley
CADalyst's CAD Clinic
Rand IMAGINiT Technologies
___________________________
the trick is to realize that there is no spoon...

 

did you change the name of the event to the same as the name of the textbox?
Private Sub TextBox1_KeyPress
works with TextBox1

if you have TextBox2
you need also a copy of the sub for that
Private Sub TextBox2_KeyPress

etc

hth
Mark

 

Always nice to have a new function/library brought to your attention...

However in this case I believe setting the Users input back to the default
value because
they entered too many decimal places is not very "friendly"

How 'bout utilising the standard VBA functions format() and round()

If Not Len(TextBox1) Then
TextBox1 = Format(Round(TextBox1, 2), "0.00")
End If

--
Regards
Tom Roberts
__________________________
MechWest Design & Drafting
Perth, Western Australia

 

However in this case I believe setting the Users input back to the default

From a programmer's point of view, they'll learn quickly what is expected
=) Actually you don't need the default but you have to some how clear out
their errant entry. As for your suggestion, it'll work but you've assumed
they typed in numbers and I haven't tried your code, but what happens if
they type in 2 periods? They shouldn't but I've seen it!

-- Mike
___________________________
Mike Tuersley
CADalyst's CAD Clinic
Rand IMAGINiT Technologies
___________________________
the trick is to realize that there is no spoon...

 

The original solution allowed for this scenario.
My second post was intended to be used in conjunction with the original
solution and simply allowed the number to be formatted to two decimal places
without referencing any additional libraries.

Ahh the little thing in life...

--
Regards
Tom Roberts
__________________________
MechWest Design & Drafting
Perth, Western Australia

 

OK. Here's a more complete redition, submitted for your consideration.

' Modoule level
Dim oFmt2 As StdFormat.StdDataFormat ' Format object, 2 place
decimal (e.g. #0.00)
Dim oFmt3 As StdFormat.StdDataFormat ' Format object, 3 place
decimal (e.g. #0.000)

Private Sub UserForm_Initialize()
Set oFmt2 = New StdFormat.StdDataFormat
oFmt2.Format = "##0.00"
Set oFmt3 = New StdFormat.StdDataFormat
oFmt3.Format = "##0.000"
End Sub

' txtGlaHgt is a userform textbox requiring a numeric entry
Private Sub txtGlaHgt_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
' cancel the update if the number isn't correct
Cancel = IIf(ValidNumber("txtGlaHgt", "Outer Area Height", oFmt3.Format,
False), False, True)
End Sub

' Check for valid numeric input in textbox & format display
Private Function ValidNumber(strCtrlName As String, strMsg As String, strFmt
As String, logAllowZero As Boolean)
Const strThisProc As String = "ValidNumber()"
Dim iRtn As Integer, logRtn As Boolean
Dim oCtrl As Control, logOK As Boolean

If ThisDrawing.logRunMode Then
On Error GoTo Err_ValidNumber
End If

Set oCtrl = Me.Controls(strCtrlName)
' check for any numeric
If IsNumeric(oCtrl.Value) Then
logOK = True
' check for negative value
If Val(oCtrl.Value) < 0 Then
logOK = False
End If
' check for illegal zero
If Not logAllowZero And Val(oCtrl.Value) = 0 Then
logOK = False
End If
Else
logOK = False
End If
If logOK Then ' set textbox to new formatted value
oCtrl.Value = Format(oCtrl.Value, strFmt)
ValidNumber = True
Else ' display message, revert to previous value
strMsg = IIf(logAllowZero, _
strMsg & " must be numeric and greater than or equal to
zero.", _
strMsg & " must be numeric and greater than zero.")
iRtn = MsgBox(strMsg, vbExclamation + vbOKOnly, "Application Message")
oCtrl.Value = oCtrl.BoundValue
ValidNumber = False
End If

Exit_ValidNumber:
Exit Function

Err_ValidNumber:
' the usual stuff
End Function
--
John Goodfellow
irtfnm
use john at goodfellowassoc dot com

 

Yeah that looks good John and works well. My only critique is there's far
more code to it than using a regular expression. 'Course the upside is you
don't need to carry around the additional reference.

-- Mike
___________________________
Mike Tuersley
CADalyst's CAD Clinic
Rand IMAGINiT Technologies
___________________________
the trick is to realize that there is no spoon...

 

I get a compile error at:

If ThisDrawing.logRunMode Then
On Error GoTo Err_ValidNumber
End If

What property does logRunMode return.
I am running A2002

--
Regards
Tom Roberts
__________________________
MechWest Design & Drafting
Perth, Western Australia

 

Correct me if I'm wrong but doesn't this code require a
reference to the Microsoft Data Formatting Object Library

--
Regards
Tom Roberts
__________________________
MechWest Design & Drafting
Perth, Western Australia

 

Correct me if I'm wrong but doesn't this code require a

Nope, you are right - nice catch! I had added a form to an existing project
where I had already referenced it so I missed it. Therefore, I take it
back, slick concept but too much code for something so simple - sorry John
=)

-- Mike
___________________________
Mike Tuersley
CADalyst's CAD Clinic
Rand IMAGINiT Technologies
___________________________
the trick is to realize that there is no spoon...

 

What property does logRunMode return.

Something specific to what he is doing that he forgot to comment out I
guess. Same prob if run in 2005.

-- Mike
___________________________
Mike Tuersley
CADalyst's CAD Clinic
Rand IMAGINiT Technologies
___________________________
the trick is to realize that there is no spoon...

 

Yep, it's a debuging flag and should be commented out. Gotta leave
something for you guys to do.
--
John Goodfellow
irtfnm
use john at goodfellowassoc dot com

 

Yeh, I agree. I penned it in my first half year of Acad VBA, before I found
the regular expressions (cleverly hidden in the scripting library?!?).
Could have used a simple Format function instead of the library, too.

 

I have a different approach - I have two functions, one checks the keystrokes as they are typed and one reformats the value when the textbox loses focus. What bugs me is that the _AfterUpdate functions do not always fire in VBA so you have to be careful about depending on them.

The keypress function eliminates all keystrokes but the characters 0-9 and the negative and decimal characters. You also have to be careful to allow the backspace character (the delete key doesn't generate a keypress event so it is fine).

The code also forces the negative sign to be the first character of the text.

Even though the code is a bit long it is reusable with many textboxes since the exact number of decimal places and allowing decimal places and negatives is optional.

Also, the other solution did not take care of the possibility of the user highlighting a portion of the text before typing. That introduces complications for example if the decimal place is highlighted and then he types another decimal place that should be allowed because the first will be automatically replaced.

(see attached example code)