Apply code to predetermined textboxes

S

sarasotavince

New Member
Joined
May 14, 2011
Messages
24
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:

Code: 
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!
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
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.

Code: 
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.

Code: 
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.

Code: 
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.
Code: 
Private Sub CommandButton1_Click()
    Call txtBoxToUCase
End Sub
 
Upvote 0
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.
 
Upvote 0
I think the code you are talking about is something like this:
Code: 
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    KeyAscii = Asc(UCase(Chr(KeyAscii)))
End Sub
 
Upvote 0
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.
 
Last edited:
Upvote 0
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
Code: 
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.
Code: 
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.
 
Upvote 0
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.
 
Upvote 0
These lines in the userform's code module link the textboxes to the Class module. Did you rename the class module CommonTextBox ?

Code: 
Dim aCommonTbx As CommonTextBox

Set aCommonTbx = New CommonTextBox

Set aCommonTbx.aTxtBox = oneTextBox
 
Upvote 0
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 :

Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Automatically insert : for hhmm textbox?
2
Replies
17
Views
609
Akuini
Akuini
A
  • Question Question
Userform "Reset" - Set user focus on a particular control, contents highlighted, ready for user to overwrite
Replies
2
Views
57
Jaafar Tribak
Jaafar Tribak
P
  • Question Question
If and ElseIf confusion !
Replies
5
Views
287
Micron
M
I
  • Solved
Code not formatting correctly in textbox userform
2 3
Replies
22
Views
736
MARK858
MARK858
M
  • Solved
Display textbox from listbox - getting 381 error
Replies
4
Views
648
maiatanaka
M

Forum statistics

Threads
1,224,527
Messages
6,179,328
Members
452,907
Latest member
Roland Deschain

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top