Apply code to predetermined textboxes

[sarasotavince]

After some unsuccessful searching, I am wondering if there is a way to apply the same kind of coding to multiple textboxes. I have seen where one call apply code to ALL textboxes on a userform, but I want to apply the code to only specific textboxes.

On the userform I have 30 textboxes. Some I have restricted to numbers only, some letters only, others are a combination. All the letter only boxes are converted to UPPERCASE. Here is the code I use:

Private Sub TextBox8_Change()
'MAKE UPPER CASE
On Error Resume Next
TextBox8 = UCase(TextBox8)
On Error GoTo 0
End Sub

Now the question. Is there a way Without copying this 20 times and changing the textbox#, how would I apply this to textboxes 1,3,5,6,10,12, etc. only?

I'm ready to take my (code)monkeying to the next level. Thanks!

 

[Ralajer]

Modified this from a post by savbill on dbforums.com. It loops through all the user form controls testing if the control's name matches your criteria, in this example it assumes all the text boxes are named TextBox(anything). It might not be exactly what you need but it a place to start.

Sub txtBoxToUCase()
Dim TB
For Each TB In UserForm1.Controls
'// "TextBox*" Where * denotes any characters after
    If TB.Name Like "TextBox*" Then
        TB.Text = UCase(TB.Text)
    End If
Next
End Sub

Applying a standard naming convention to the text boxes based on content type such as TB_Str_1 you can limited it to apply to just the text boxes containing strings.

For Each TB In UserForm1.Controls
    If TB.Name Like "TB_Str*" Then
        TB.Text = UCase(TB.Text)
    End If
Next

You could also test the text box's content.

For Each TB In UserForm1.Controls
    If TB.Name Like "TextBox*" Then
        '// If the content isn't numeric and not empty
        If Not IsNumeric(TB.Text) And Len(TB.Text) > 0 Then
            TB.Text = UCase(TB.Text)
        End If
    End If
Next

Call it when you press the command button or wherever it is best suits your needs.

Private Sub CommandButton1_Click()
    Call txtBoxToUCase
End Sub

 

[sarasotavince]

This is good stuff. However, what I liked about the original code is that it converted the text to UPPERCASE as the user typed whereas (it appears to me) this code must be called, thus converting after the fact. I will study this and likely find an application for something like it in the future. Your prompt reply is much appreciated.

 

[mikerickson]

I think the code you are talking about is something like this:

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    KeyAscii = Asc(UCase(Chr(KeyAscii)))
End Sub

 

[Ralajer]

Apologies for my misunderstanding. Mike's code will work of course; although, you'll still have to do it for each text box. I don't think there is a way to use the same code for different events other than a call to a common function or sub from each event. Which would have more utility if it was more than one line of code.

 

[mikerickson]

One way to get controls to react to common code is to use a Class module.

In the VBEditor, insert a Class module into your project.
Use the Properties window to change the name from Class1 to CommonTextBox
Put this code in that class module

Option Explicit

Public WithEvents aTxtBox As MSForms.TextBox

Private Sub aTxtBox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    KeyAscii = Asc(UCase(Chr(KeyAscii)))
End Sub

Put this code in the userform's code module.

Private Sub UserForm_Initialize()
    Dim oneTextBox As Variant
    Dim aCommonTbx As CommonTextBox
    Set myTextBoxes = New Collection
    
    For Each oneTextBox In Array(TextBox1, TextBox3, TextBox4)

        Rem create new instance of CommonTextBox
        Set aCommonTbx = New CommonTextBox

        Rem assign userform text box to new instance
        Set aCommonTbx.aTxtBox = oneTextBox

        Rem save new instance in collection
        myTextBoxes.Add aCommonTbx, Key:=oneTextBox.Name
        
        Rem housekeeping
        Set aCommonTbx = Nothing
    Next oneTextBox
End Sub

Private Sub UserForm_Terminate()
    Rem good practice of cleaning up user defined objects
    Dim aCommonTbx As CommonTextBox

    Rem de-instansize all the CommonTextBox objects
    For Each aCommonTbx In myTextBoxes
        Set aCommonTbx = Nothing
    Next aCommonTbx

    Rem housekeeping
    Set myTextBoxes = Nothing
End Sub

Every time the user enters something into Textbox1, Textbox3 or Textbox4, the aTxtBox_KeyPress routine will run, forcing the entry into upper case.

 

[sarasotavince]

Looks like I will be going to school for CLASS MODULES.

Thank you RALAJER and MIKERICKSON for your input.

 

[sarasotavince]

I have tried the code...I can't get it to work. I guess what I don't understand is how the class module code is related to the userform code? I thought there would be a common "term" in both, linking them. Please explain why or why not. Thanks in advance.

 

[mikerickson]

These lines in the userform's code module link the textboxes to the Class module. Did you rename the class module CommonTextBox ?

Dim aCommonTbx As CommonTextBox

Set aCommonTbx = New CommonTextBox

Set aCommonTbx.aTxtBox = oneTextBox

 

[Jaafar Tribak]

If you want NOT to use a seperate Class and keep the whole code inside the Form module, you may want to use the code below :

Option Explicit

Public WithEvents TxtBoxEvents As MSForms.TextBox
Private oFormsArray() As UserForm1

Private Sub TxtBoxEvents_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
 KeyAscii = Asc(UCase(Chr(KeyAscii)))
End Sub

Private Sub UserForm_Initialize()

    If VBA.UserForms.Count > 1 Then Exit Sub

    [COLOR=Green]'rest of your code here....[/COLOR]

End Sub

Private Sub UserForm_Activate()

    Dim oCtl As Control
    Dim lCounter As Long
    
    lCounter = 0
    For Each oCtl In Me.Controls
        If TypeOf oCtl Is MSForms.TextBox Then
            ReDim Preserve oFormsArray(lCounter)
            Set oFormsArray(lCounter) = New UserForm1
            Set oFormsArray(lCounter).TxtBoxEvents = oCtl
            lCounter = lCounter + 1
        End If
    Next

[COLOR=Green]'rest of your code here....[/COLOR]
End Sub