Reduce code
 

I have a several forms that use some code form Dave Peterson web site, to
limit the input on textboxes to "Numbers", is there a way to have a
"function" or like, to help reduce code size by eliminating some of the
repeated code for each textbox (about 50). code is below

I think I am looking for a way to reference the "live" textbox.

Private Sub TextBox11_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case Asc("0") To Asc("9")
Case Asc("-")
If InStr(1, Me.TextBox11.Text, "-") 0 Or Me.TextBox11.SelStart 0
Then
KeyAscii = 0
End If
Case Asc(".")
If InStr(1, Me.TextBox11.Text, ".") 0 Then
KeyAscii = 0
End If
Case Else
KeyAscii = 0
End Select
End Sub

Reduce code
 

I'd use something like:

Option Explicit
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Call CheckValidity(KeyAscii, Me.TextBox1)
End Sub

Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Call CheckValidity(KeyAscii, Me.TextBox2)
End Sub

Private Sub CheckValidity(KeyAscii As MSForms.ReturnInteger, _
myTB As MSForms.TextBox)
Select Case KeyAscii
Case Asc("0") To Asc("9")
Case Asc("-")
If InStr(1, myTB.Text, "-") 0 _
Or myTB.SelStart 0 Then
KeyAscii = 0
End If
Case Asc(".")
If InStr(1, myTB.Text, ".") 0 Then
KeyAscii = 0
End If
Case Else
KeyAscii = 0
End Select
If KeyAscii = 0 Then
Beep
End If
End Sub

Excel 2003 - SPB wrote:

I have a several forms that use some code form Dave Peterson web site, to
limit the input on textboxes to "Numbers", is there a way to have a
"function" or like, to help reduce code size by eliminating some of the
repeated code for each textbox (about 50). code is below

I think I am looking for a way to reference the "live" textbox.

Private Sub TextBox11_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case Asc("0") To Asc("9")
Case Asc("-")
If InStr(1, Me.TextBox11.Text, "-") 0 Or Me.TextBox11.SelStart 0
Then
KeyAscii = 0
End If
Case Asc(".")
If InStr(1, Me.TextBox11.Text, ".") 0 Then
KeyAscii = 0
End If
Case Else
KeyAscii = 0
End Select
End Sub

--

Dave Peterson

Reduce code
 

Paste the following function in a VBA code module:

Public Function TextBox_KeyPress(WhichTB As Control, ByVal KeyAscii As
MSForms.ReturnInteger) As Boolean
Select Case KeyAscii
Case Asc("0") To Asc("9")
TextBox_KeyPress = True
Case Asc("-")
If InStr(1, WhichTB.Text, "-") 0 Or WhichTB.SelStart 0 Then
TextBox_KeyPress = False
Else
TextBox_KeyPress = True
End If
Case Asc(".")
If InStr(1, WhichTB.Text, ".") 0 Then
TextBox_KeyPress = False
Else
TextBox_KeyPress = True
End If
Case Else
TextBox_KeyPress = False
End Select
End Function


Then change the KeyPress event code for each textbox to call the function,
like this:

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If TextBox_KeyPress(Me.TextBox1, KeyAscii) = False Then KeyAscii = 0
End Sub

Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If TextBox_KeyPress(Me.TextBox2, KeyAscii) = False Then KeyAscii = 0
End Sub

Private Sub TextBox3_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If TextBox_KeyPress(Me.TextBox3, KeyAscii) = False Then KeyAscii = 0
End Sub

Hope this helps,

Hutch

"Excel 2003 - SPB" wrote:

I have a several forms that use some code form Dave Peterson web site, to
limit the input on textboxes to "Numbers", is there a way to have a
"function" or like, to help reduce code size by eliminating some of the
repeated code for each textbox (about 50). code is below

I think I am looking for a way to reference the "live" textbox.

Private Sub TextBox11_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case Asc("0") To Asc("9")
Case Asc("-")
If InStr(1, Me.TextBox11.Text, "-") 0 Or Me.TextBox11.SelStart 0
Then
KeyAscii = 0
End If
Case Asc(".")
If InStr(1, Me.TextBox11.Text, ".") 0 Then
KeyAscii = 0
End If
Case Else
KeyAscii = 0
End Select
End Sub