Disabling multiple textboxes

[austin350s10]

I am working on a userform with a bunch of textboxes on it. In order for the form to work properly all of the textboxes can only contain numbers 1 through 99. No special characters or letters, just numbers. The code below does exactly what I want but it only works on one text box at a time. It would require we to write the below code for each textbox on the userform to get the desired result but that seems like a lot of unnecessary code.

working code:


Private Sub textbox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If InStr("0123456789", Chr(KeyAscii)) = 0 Then KeyAscii = 0
End Sub



However I am looking for a cleaner, shorter way to make the above script work for all the textboxes on the form. I tried the following code with no success:

not working code:


Private Sub UserForm_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Dim obj As Control
For Each obj In Me.Controls
If TypeName(obj) = "TextBox" Then
If InStr("0123456789", Chr(KeyAscii)) = 0 Then KeyAscii = 0
End If
Next obj
End Sub



My hope here was that it would apply the "working" script to each textbox in the userform.
What am I doing wrong here? To make this work do I really need to write the same script for each textbox on my userform?

[mdmackillop]

Have a look at this thread (http://www.vbaexpress.com/forum/showthread.php?t=24799)

[JKwan]

put this into your userform


Option Explicit
Dim TextBoxes() As clsControls
Private Sub UserForm_Initialize()
Dim cControl As MSForms.Control
Dim TextBoxNumber As Long

For Each cControl In Me.Controls
Select Case TypeName(cControl)
Case Is = "TextBox"
TextBoxNumber = TextBoxNumber + 1
ReDim Preserve TextBoxes(TextBoxNumber)
Set TextBoxes(TextBoxNumber) = New clsControls
TextBoxes(TextBoxNumber).SetTextBox cControl
cControl.Tag = TextBoxNumber
End Select
Next
End Sub


put this into a Class module (call it clsControls)


Option Explicit
Private WithEvents txtTextBox As MSForms.TextBox
Sub SetTextBox(ByVal cControl As MSForms.TextBox)
Set txtTextBox = cControl
End Sub
Private Sub txtTextBox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If InStr("0123456789", Chr(KeyAscii)) = 0 Then KeyAscii = 0
End Sub