PDA

View Full Version : Solved: code to work for 12 TextBoxes of a form



omp001
01-14-2012, 03:15 PM
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.
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

Bob Phillips
01-14-2012, 07:03 PM
Create a class module, call it clsFormEvents, and add this code



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


Then in the form, add this code





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

omp001
01-15-2012, 03:38 AM
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?

Set tbEvents = New clsFormEvents
Set tbEvents.txtBox = Me.TextBox1
collTextBoxes.Add tbEvents
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
01-15-2012, 07:57 AM
Yes, like this


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

omp001
01-15-2012, 12:18 PM
Yes, Bob. As always, simple and efficient. Works like a charm!
Thank you so much.

Bob Phillips
01-16-2012, 03:07 AM
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.