Solved: code to work for 12 TextBoxes of a form

[omp001]

Hi peolple.
I have the code below that allows only number into 'TextBox1'.
Is it possible just one code to work same way for all the 12 textboxes of a form?
If yes, please, give me a guidance.
thanks in advance for all.
[vba]Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If Shift Then KeyCode = 0
Select Case KeyCode
Case 8, 13, 46, 48 To 57, 96 To 105, 109, 110, 189, 190
Case Else
KeyCode = 0
End Select
End Sub [/vba]

[Bob Phillips]

Create a class module, call it clsFormEvents, and add this code

[vba]

Option Explicit

Public WithEvents txtBox As MSForms.TextBox

Private Sub txtBox_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If Shift Then KeyCode = 0
Select Case KeyCode
Case 8, 13, 46, 48 To 57, 96 To 105, 109, 110, 189, 190
Case Else
KeyCode = 0
End Select
End Sub
[/vba]

Then in the form, add this code

[vba]



Private collTextBoxes As Collection


Private Sub UserForm_Initialize()
Dim tbEvents As clsFormEvents

Set collTextBoxes = New Collection

Set tbEvents = New clsFormEvents
Set tbEvents.txtBox = Me.TextBox1
collTextBoxes.Add tbEvents

Set tbEvents = New clsFormEvents
Set tbEvents.txtBox = Me.TextBox2
collTextBoxes.Add tbEvents
End Sub[/vba]

[omp001]

Hi Bob.
Thank you.
It's working for TextBoxes #1 and #2.

To work for all the TextBoxes I have to repeat the piece of code below, changing TextBox#, as many times as the amount of TextBoxes on the Form. Is it?

[vba]Set tbEvents = New clsFormEvents
Set tbEvents.txtBox = Me.TextBox1
collTextBoxes.Add tbEvents [/vba]
There would be a way to extend the code for all 12 TextBoxes (it could be up to 30) without having to repeat that part of the code?
Something like: TextBox 1 to 12, All TextBoxes in Form, TextBox Collection?

Thanks again for your help.
Nice Sunday.

[Bob Phillips]

Yes, like this
[vba]

Dim tbEvents As clsFormEvents
Dim ctl As Object

Set collTextBoxes = New Collection

For Each ctl In Me.Controls

If TypeName(ctl) = "TextBox" Then

Set tbEvents = New clsFormEvents
Set tbEvents.txtBox = ctl
collTextBoxes.Add tbEvents
End If
Next ctl[/vba]

[omp001]

Yes, Bob. As always, simple and efficient. Works like a charm!
Thank you so much.

[Bob Phillips]

I have to admit that my preferred way is to set the Tag property for all items that I want to include in this control array, and test that in the loop. This way, certain of that control type (your textboxes) can easily be exempted from this check.