VBA Code to force uppercase text in multiple textboxes

[D_Rod]

Hello,
I am new to excel an I was hoping to get some help.
I have created a userform with multiple text boxes, I would like to force the entered text to Upper case. I currently am using a ucase syntax, but it looks rookie and slows my code down


This is what I have so far.

Private Sub tb_Q1_Change()
tb_Q1.Text = UCase(tb_Q1.Text)

End Sub
Private Sub tb_Q2_Change()
tb_Q2.Text = UCase(tb_Q2.Text)
End Sub

I know there is a way to use a class module but I am unfamiliar with set up.

Can anyne help?
I would appreciate code examples for the class Module and the regular module

Thanks in advance

 

[Domenic]

Your code seems fine. Although, it could be re-written as follows..

Private Sub tb_Q1_Change()
    With Me.tb_Q1
        .Text = UCase(.Text)
    End With
End Sub

Another option might be to use the BeforeUpdate event instead of the Change event...

Private Sub tb_Q1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    With Me.tb_Q1
        .Text = UCase(.Text)
    End With
End Sub

With the Change event, it gets triggered with each keystroke. Whereas with the BeforeUpdate event, it only gets triggered when you press the Tab key, or Enter key, or click on another control.

Hope this helps!

 

[D_Rod]

Thank you for the reply. I was hoping that their was a way to create a class module to streamline the process an avoid having to write the code multiple times for 10 different textboxes

Would his be possible?

Thank you again.

 

[Rick Rothstein]

Thank you for the reply. I was hoping that their was a way to create a class module to streamline the process an avoid having to write the code multiple times for 10 different textboxes

What are the names of your 10 textboxes?

 

[Domenic]

I see I missed the keyword "multiple" in your original post. In that case, first insert a class module (Visual Basic Editor >> Insert >> Class Module). Then, under the Properties window (F4), name the class module clsTBoxes. Then, place the following code into the code module for the class...

Option Explicit


Public WithEvents MyTBoxes As MSForms.TextBox


Private Sub MyTBoxes_Change()
    With Me.MyTBoxes
        .Text = UCase(.Text)
    End With
End Sub

Then, under the Project explorer window (Ctrl+R), right-click your userform and select View Code. Then place the following code into the code module for the userform...

Option Explicit


Dim colTBoxes As New Collection


Private Sub UserForm_Initialize()
    Dim cTBoxes As clsTBoxes
    Dim Ctrl As Control
    For Each Ctrl In Me.Controls
        If TypeName(Ctrl) = "TextBox" Then
            Set cTBoxes = New clsTBoxes
            Set cTBoxes.MyTBoxes = Ctrl
            colTBoxes.Add cTBoxes
        End If
    Next Ctrl
End Sub

Hope this helps!