Userform textbox uppercase

[ipbr21054]

Morning,
I have a userform which has Textbox 1,2,3,4,5

The code below works but i wish to apply it to all text boxes without writing it 5 times.
Please can you advise.

Private Sub TextBox2_Change()    TextBox2 = UCase(TextBox2)
End Sub

 

[Jaafar Tribak]

Morning,
I have a userform which has Textbox 1,2,3,4,5

The code below works but i wish to apply it to all text boxes without writing it 5 times.
Please can you advise.

Private Sub TextBox2_Change()    TextBox2 = UCase(TextBox2)
End Sub

5 textboxes is not many so you could just repeat the code for each textbox.

However, if you want to have a generic change event for all textboxes then you could use a class module approach as follows :

1- Add a new class module to your VBProject Class1 and place the following code in the newly added class module:

Option Explicit

Public WithEvents TxtBx As MsForms.TextBox

Private Sub TxtBx_Change()
    TxtBx = UCase(TxtBx)
End Sub

2- In the UserForm Module, place the following code :

Option Explicit

Private oCol As New Collection

Private Sub UserForm_Initialize()
    Dim oCtrl As Control, oClass As Class1
    
    For Each oCtrl In Me.Controls
        If TypeOf oCtrl Is MsForms.TextBox Then
            Set oClass = New Class1
            Set oClass.TxtBx = oCtrl
            oCol.Add oClass
        End If
    Next oCtrl
End Sub

I assumed the textboxes are on a UserForm not on a worksheet.

 

[ipbr21054]

Thank,
Yes a userform.

I thought it would be tidy have 1 code as opposed to 5.

Many thanks for the reply

 

[Rick Rothstein]

Jaafar has given you code which will handle all of the TextBoxes on your UserForm. Below is code which specifically handles the five TextBoxes you said are on the UserForm (TextBox1 thru TextBox5)...

' Class Module Code
Public WithEvents TextBoxEvents As MsForms.TextBox

Private Sub TextBoxEvents_Change()
  Application.EnableEvents = False
  TextBoxEvents.Text = UCase(TextBoxEvents.Text)
  Application.EnableEvents = True
End Sub

' UserForm Module Code
Dim AobjTextBoxes(1 To 5) As New Class1

Private Sub UserForm_Initialize()
  Dim X As Long, objControl As Control
  For X = 1 To 5
    Set AobjTextBoxes(X).TextBoxEvents = Me.Controls("TextBox" & X)
  Next
End Sub

 

[Jaafar Tribak]

Jaafar has given you code which will handle all of the TextBoxes on your UserForm. Below is code which specifically handles the five TextBoxes you said are on the UserForm (TextBox1 thru TextBox5)...

' Class Module Code
Public WithEvents TextBoxEvents As MsForms.TextBox

Private Sub TextBoxEvents_Change()
  Application.EnableEvents = False
  TextBoxEvents.Text = UCase(TextBoxEvents.Text)
  Application.EnableEvents = True
End Sub

' UserForm Module Code
Dim AobjTextBoxes(1 To 5) As New Class1

Private Sub UserForm_Initialize()
  Dim X As Long, objControl As Control
  For X = 1 To 5
    Set AobjTextBoxes(X).TextBoxEvents = Me.Controls("TextBox" & X)
  Next
End Sub

Hi Rick,

Any reason you temporarly disable the application events ?

 

[Rick Rothstein]

Hi Rick,

Any reason you temporarly disable the application events ?

Assigning the upper case text back to the same TextBox would kick off the Change event a second time... disabling events temporarily stops that from happening. No actual harm in letting the event happen twice for what the OP asked for, but it does make code do extra work.

 

[Jaafar Tribak]

Assigning the upper case text back to the same TextBox would kick off the Change event a second time... disabling events temporarily stops that from happening. No actual harm in letting the event happen twice for what the OP asked for, but it does make code do extra work.

Disabling Application events has no effect on userform controls or activex , commandbars events etc ..

But we could use a boolean variable to mimic Application.EnableEvents for this scenario

Option Explicit

Public WithEvents TxtBx As MsForms.TextBox
Private EnableEvents As Boolean

Private Sub Class_Initialize()
    EnableEvents = True
End Sub

Private Sub TxtBx_Change()
    If EnableEvents = False Then EnableEvents = True: Exit Sub
    EnableEvents = False
    TxtBx = UCase(TxtBx)
End Sub