excel VBA text
 

resrict a VBA text box to numbers only. how would you do this?

--
draca75

excel VBA text
 

resrict a VBA text box to numbers only. how would you do this?

Assuming we are talking about TextBoxes on UserForms, here are some
solutions which I've posted in the past. There is code below for both,
entries with digits only and for entries with decimal points (Copy/Paste all
of the code from the section that applies to your requirements). The
routines work quite well and protects the TextBox from pasting non-numeric
entries (the user can paste valid data though) as well as stopping
non-numeric keypresses. (Be wary of KeyPress only type solutions which may
be posted later on as they can't protect against bad data being Paste'd in.)

Rick

For typing digits only in the TextBox
=====================================
Dim LastPosition As Long

Private Sub TextBox1_Change()
Static LastText As String
Static SecondTime As Boolean
If Not SecondTime Then
With TextBox1
If .Text Like "*[!0-9]*" Then
Beep
SecondTime = True
.Text = LastText
.SelStart = LastPosition
Else
LastText = .Text
End If
End With
End If
SecondTime = False
End Sub

Private Sub TextBox1_MouseDown(ByVal Button As Integer, _
ByVal Shift As Integer, _
ByVal X As Single, _
ByVal Y As Single)
With TextBox1
LastPosition = .SelStart
'Place any other MouseDown event code here
End With
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
With TextBox1
LastPosition = .SelStart
'Place any other KeyPress checking code here
End With
End Sub


For typing floating point numbers in the TextBox
=========================================
' Set the maximum number of digits before the
' decimal point in the MaxWhole constant. Set
' the maximum number of digits after the decimal
' point in the MaxDecimal constant.
Dim LastPosition As Long

Private Sub TextBox1_Change()
Static LastText As String
Static SecondTime As Boolean
Const MaxDecimal As Integer = 4
Const MaxWhole As Integer = 2
With TextBox1
If Not SecondTime Then
If .Text Like "*[!0-9.]*" Or _
.Text Like "*.*.*" Or _
.Text Like "*." & String$(1 + MaxDecimal, "#") Or _
.Text Like String$(MaxWhole, "#") & "[!.]*" Then
Beep
SecondTime = True
.Text = LastText
.SelStart = LastPosition
Else
LastText = .Text
End If
End If
End With
SecondTime = False
End Sub

Private Sub TextBox1_MouseDown(ByVal Button As Integer, _
ByVal Shift As Integer, _
ByVal X As Single, _
ByVal Y As Single)
With TextBox1
LastPosition = .SelStart
'Place any other MouseDown event code here
End With
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
With TextBox1
LastPosition = .SelStart
'Place any other KeyPress checking code here
End With
End Sub

NOTES
=========================================
Note that you will have check for the Text property containing a single
character consisting of a decimal point since that must be allowed as a
starting character. If you want to allow negative, as well as positive
values, then use this If statement in place of the second If statement in
the Text1_Change event code above:

If .Text Like "*[!0-9.+-]*" Or _
.Text Like "*.*.*" Or _
.Text Like "*." & String$(1 + MaxDecimal, "#") Or _
.Text Like "*" & String$(MaxWhole, "#") & "[!.]*" Or _
.Text Like "?*[+-]*" Then

Note that now you will have to check the Text property for this one to see
if it contains a single plus, minus or decimal point.

I guess I should mention that I'm in the US where the decimal point is a
"dot". If your decimal point is some other characters, then make the obvious
substitutions in the If-Then tests above; or you could query the system for
the decimal point character, store it in a variable and concatenate that
into the string values above in place of the decimal point ("dot") that I
show above. In keeping with the non-APIness of this solution, here is what I
use to get the system's decimal point.

DecimalPointSymbol = Format$(0, ".")

excel VBA text
 

Hi Rick,

One very small point:

=============
[...]
In keeping with the non-APIness of this solution, here is what I
use to get the system's decimal point.

DecimalPointSymbol = Format$(0, ".")
[...]
=============

Perhaps, alternatively:

DecimalPointSymbol = Application.DecimalSeparator


---
Regards.
Norman

excel VBA text
 

Yeah, a carry-over from my compiled VB days (as was the code also).

The help files are not completely clear to me... does the DecimalSeparator
property apply to the VBA world as well, or only to the worksheet world? The
Format function call I offered returns the Windows decimal separator,
period, not Excel's whereas I presume the DecimalSeparator property returns
the decimal separator being used on the worksheet (which usually would be
the same as Windows, but not necessarily). I know there is a
UseSystemSeparators property which can be set to False to force the
worksheet to use Windows decimal separator even if Excel's is different;
but, again, does this extend into the VBA world as well?

Rick


"Norman Jones" wrote in message
...
Hi Rick,

One very small point:

=============
[...]
In keeping with the non-APIness of this solution, here is what I use to
get the system's decimal point.

DecimalPointSymbol = Format$(0, ".") [...]
=============

Perhaps, alternatively:

DecimalPointSymbol = Application.DecimalSeparator


---
Regards.
Norman

excel VBA text
 

Hi Rick,

Application.DecimalSeparator returns
the relevant Windows setting. However,
it is an (Excel) application property and
would not be recognised, say, by Word
VBA.

In my previous response I omitted to
compliment you on the fact that you actively
address potential international issues, so I am
happy to rectify that now.



---
Regards.
Norman

"Rick Rothstein (MVP - VB)" wrote in
message ...
Yeah, a carry-over from my compiled VB days (as was the code also).

The help files are not completely clear to me... does the DecimalSeparator
property apply to the VBA world as well, or only to the worksheet world?
The Format function call I offered returns the Windows decimal separator,
period, not Excel's whereas I presume the DecimalSeparator property
returns the decimal separator being used on the worksheet (which usually
would be the same as Windows, but not necessarily). I know there is a
UseSystemSeparators property which can be set to False to force the
worksheet to use Windows decimal separator even if Excel's is different;
but, again, does this extend into the VBA world as well?

Rick


"Norman Jones" wrote in message
...
Hi Rick,

One very small point:

=============
[...]
In keeping with the non-APIness of this solution, here is what I use to
get the system's decimal point.

DecimalPointSymbol = Format$(0, ".") [...]
=============

Perhaps, alternatively:

DecimalPointSymbol = Application.DecimalSeparator


---
Regards.
Norman