0 votes
in VBA by Expert (848 points)

Hello everyone

I have textboxes named txtOwner1, txtOwner2 .... to txtOwner12

How can I put specific code in the event of change of specific textboxes within this group?

I mean I want to put code for txtOwner2 / txtOwner5 / txtOwner8 / txtOwner11 only ..

The code would be display a message box of the textbox number

Say ::

Private Sub txtOwner2_Change()
    Dim x
    x = 2 '(should be proper to the textbox name)
    MsgBox "The TextBox Number Is " & x
End Sub

Is the class module is the best approach for that .. and if possible how can I do that by class module?


In brief I need to put the same code for the textboxes txtOwner2 / txtOwner5 / txtOwner8 / txtOwner11 and at the same time I don't want to repeat the lines of code for each textbox .. as the original form has about 45 textboxes so it will be very tiring to copy the same code to all those textboxes and of course the code will be lengthy ..

Thanks advanced for help

1 Answer

+1 vote
by Super Expert (2.9k points)
selected by
Best answer

A class module is the best approach for sharing code between multiple textboxes (ActiveX or UserForms). Here's how you would do it:

  1. Insert a Class Module (Insert > Class Module)
  2. Rename the class module cTextBox
  3. Paste the following VBA code
    1. Public WithEvents TextBoxX As MSForms.TextBox
      Private Sub TextBoxX_Change()
          Dim x
          x = CInt(Replace(TextBoxX.Name, "TextBox", ""))
          If x Mod 3 = 2 Then '2, 5, 8 ,11, etc
              'put your code here for textboxes 2, 5, 8, 11, etc
              MsgBox "The TextBox Number Is " & x
          End If
      End Sub

Steps for ActiveX TextBoxes

If you're using ActiveX TextBoxes, insert a Regular Module and paste the following code:

Dim TheTextBoxes() As New cTextBox

Sub ActivateActiveXTextBoxes()
  Dim sh As Shape
  Dim iTextBoxCount As Long

  ReDim TheTextBoxes(1 To 1)

  iTextBoxCount = 0
  For Each sh In ActiveSheet.Shapes
    If sh.OLEFormat.Object.OLEType = xlOLEControl Then
      iTextBoxCount = iTextBoxCount + 1
      ReDim Preserve TheTextBoxes(1 To iTextBoxCount)
      Set TheTextBoxes(iTextBoxCount).TextBoxX = sh.OLEFormat.Object.Object
    End If
End Sub

Sub DeactivateActiveXTextBoxes()
  Dim iTexts As Long

  On Error Resume Next
  For iTexts = LBound(TheTextBoxes) To UBound(TheTextBoxes)
    Set TheTextBoxes(iTexts).TextBoxX = Nothing
End Sub

Run the ActivateActiveXTextBoxes subroutine. (Note: It'd be a good practice to call this subroutine each time the workbook is opened by running it from the Workbook_Open VBA Workbook Event.)

Now anytime you make a change to one of the ActiveX textboxes, the name is extracted and only textboxes following the 2, 5, 8, 11 pattern are controlled by the conditional statement in the class module.

Steps for UserForm TextBoxes

If you're using UserForm TextBoxes, Paste the following UserForm_Initialize macro into your UserForm module code section. 

Dim TheTextBoxes() As New cTextBox

Private Sub UserForm_Initialize()
Dim ctrl As Control
Dim iTextBoxCount As Long
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "TextBox" Then
            iTextBoxCount = iTextBoxCount + 1
            ReDim Preserve TheTextBoxes(1 To iTextBoxCount)
            Set TheTextBoxes(iTextBoxCount).TextBoxX = ctrl
        End If
    Next ctrl
End Sub

You don't need a "deactivate" subroutine since the memory is cleared when the UserForm is closed.

by Expert (848 points)
edited by

Thank you very much Mr. Ryan

That seems wonderful solution .. Just one note : the textboxes are on a userform: what changes should I do so as to deal with the textboxes on the userform "UserForm1" for example?
Sorry for not explaining that from the beginning .. but I think the same approach will be applied

* Another point when testing the code in this line I got an error

Set theTextBoxes(iTexts).TheText = Nothing

Would I replace TheText with TextBoxX

by Super Expert (2.9k points)
No problem - I've updated my answer to include steps for UserForms, as well. You're correct about the typo in the deactivate routine. I changed the "TheText" typo to "TextBoxX" in the revised answer, as well.
by Expert (848 points)
You are really amazing. That's exactly what I am searching for.

Thank you very very much.

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

Looking for something else? Hire our professional VBA Help, instead.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

ParserMonster $25 Amazon Gift Card
Hightree $10 Amazon Gift Card
Thales1 $10 Amazon Gift Card
runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.


For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.