Text Box Data Validation

I have some text boxes on a form that I want the user to be limited to only
numeric entry. I found this code in an earlier thread:

Private Sub TextBox1_KeyDown(ByVal KeyCode As _
MSForms.ReturnInteger, ByVal Shift As Integer)
If Shift = 2 Then
If KeyCode = 86 Then KeyCode = 0
End If
End Sub


Private Sub TextBox1_KeyPress(ByVal KeyAscii _
As MSForms.ReturnInteger)
Select Case KeyAscii
Case 46
If InStr(TextBox1.Text, ".") > 0 Then _
KeyAscii = 0
Case 48 To 57
Case Else
KeyAscii = 0
End Select
End Sub


This works great, but I need decimal entries to be allowed as well, and when
the user presses the "." key an error is returned. Since I'm fairly new at
this, and completely in the dark about limiting key entries, I need some help!

Hi dutty,

Something like this should work:

Private Sub TextBox1_KeyPress(ByVal KeyAscii _
As MSForms.ReturnInteger)
If Not ((KeyAscii >= Asc("0") And _
KeyAscii <= Asc("9")) Or (KeyAscii = Asc(".") _
And InStr(TextBox1.Text, ".") = 0)) Then
Interaction.Beep
KeyAscii = 0
End If
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


dutty wrote:
> I have some text boxes on a form that I want the user to be limited
> to only numeric entry. I found this code in an earlier thread:
>
> Private Sub TextBox1_KeyDown(ByVal KeyCode As _
> MSForms.ReturnInteger, ByVal Shift As Integer)
> If Shift = 2 Then
> If KeyCode = 86 Then KeyCode = 0
> End If
> End Sub
>
>
> Private Sub TextBox1_KeyPress(ByVal KeyAscii _
> As MSForms.ReturnInteger)
> Select Case KeyAscii
> Case 46
> If InStr(TextBox1.Text, ".") > 0 Then _
> KeyAscii = 0
> Case 48 To 57
> Case Else
> KeyAscii = 0
> End Select
> End Sub
>
>
> This works great, but I need decimal entries to be allowed as well,
> and when the user presses the "." key an error is returned. Since
> I'm fairly new at this, and completely in the dark about limiting key
> entries, I need some help!

You 'da man, Jake!!

Thanks so much!

"Jake Marx" wrote:

> Hi dutty,
>
> Something like this should work:
>
> Private Sub TextBox1_KeyPress(ByVal KeyAscii _
> As MSForms.ReturnInteger)
> If Not ((KeyAscii >= Asc("0") And _
> KeyAscii <= Asc("9")) Or (KeyAscii = Asc(".") _
> And InStr(TextBox1.Text, ".") = 0)) Then
> Interaction.Beep
> KeyAscii = 0
> End If
> End Sub
>
> --
> Regards,
>
> Jake Marx
> MS MVP - Excel
> www.longhead.com
>
> [please keep replies in the newsgroup - email address unmonitored]
>
>
> dutty wrote:
> > I have some text boxes on a form that I want the user to be limited
> > to only numeric entry. I found this code in an earlier thread:
> >
> > Private Sub TextBox1_KeyDown(ByVal KeyCode As _
> > MSForms.ReturnInteger, ByVal Shift As Integer)
> > If Shift = 2 Then
> > If KeyCode = 86 Then KeyCode = 0
> > End If
> > End Sub
> >
> >
> > Private Sub TextBox1_KeyPress(ByVal KeyAscii _
> > As MSForms.ReturnInteger)
> > Select Case KeyAscii
> > Case 46
> > If InStr(TextBox1.Text, ".") > 0 Then _
> > KeyAscii = 0
> > Case 48 To 57
> > Case Else
> > KeyAscii = 0
> > End Select
> > End Sub
> >
> >
> > This works great, but I need decimal entries to be allowed as well,
> > and when the user presses the "." key an error is returned. Since
> > I'm fairly new at this, and completely in the dark about limiting key
> > entries, I need some help!
>