Controlling textbox content on a userform

[BerylM]

Hi folks, I just found your group and like the look of it! Can I ask a
question to get started?

I have a userform with two textboxes and an OK button, and the content
of one of the textboxes needs to always be numerical - is there a way
to restrict the content so that it will only accept numbers? I'm
fairly certain there's no property to do it (although happy to be
proved wrong if there is!) but I was thinking maybe something like
this:

Private Sub txtNo_Change()
If .value <> [numerical] then msgbox "Numbers only, please! Try
again", vbinformation
End Sub

Needless to say I don't know what to put where I've got [numerical]
above! Any assistance would be much appreciated!

Many thanks
BerylM

 

[Karl E. Peterson]

I have a userform with two textboxes and an OK button, and the content
of one of the textboxes needs to always be numerical - is there a way
to restrict the content so that it will only accept numbers?

There are a number of approaches. All with different drawbacks. If you
want to prevent a user from ever entering anything other than numbers (or
other special chars) with the keyboard, you'd want to use the KeyPress
events. Something like this:

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Debug.Print KeyAscii
Select Case KeyAscii
Case 48 To 57
' Asc("0") to Asc("9") - these are okay
Case Else
KeyAscii = 0
End Select
End Sub

That said, you're still presented with the issue of the user pasting other
text into the textbox. It's up to you how to handle that, of course, but
here's one approach to validate every time the textbox content changes:

Private Sub TextBox1_Change()
Static PreservedText As String
Dim ThisChar As Long
Dim i As Long
' Danged users, gotta check to see if they
' pasted something other than our valid chars
' into the textbox!
With TextBox1
For i = 1 To Len(.Text)
ThisChar = Asc(Mid$(.Text, i, 1))
If ThisChar < 48 Or ThisChar > 57 Then
' Illegal input, revert!
.Text = PreservedText
Exit For
End If
Next i
' Save copy of new textbox contents.
PreservedText = .Text
End With
End Sub

Later... Karl

 

[Kodeworks]

BerylM

Try using the IsNumeric function in the BeforeUpdate event on the txtNo
textbox

Private Sub txtNo_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Dim strText As String

strText = Me.txtNo.Text
If Not IsNumeric(strText) Then
MsgBox "Numbers only, please! Try again", vbInformation
Cancel = True 'don't let the user exit the textbox
Else
'Re-format for cosmetics if necessary
Me.txtNo.Text = Format(strText, "###,##0")
Cancel = False ' let the user exit
End If
End Sub

Sunil Jadwani
Kodeworks - Business Automation Solutions
www.kodeworks.com

 

[Karl E. Peterson]

Try using the IsNumeric function

Hmmmmmm, okay...

?isnumeric("1e0")

?isnumeric("456d123")

?isnumeric("100,00.00")

?isnumeric("1,2,3,4,5")

?isnumeric("($1,23,,3.4,,,5,,E67$)")

 

[Kodeworks]

Karl

Nice one. There's an eye-opener.

Sunil Jadwani
Kodeworks - Business Automation Solutions
wwww.kodeworks.com