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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.