0 votes
in VBA by Expert (916 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 (3.2k 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 (916 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 (3.2k 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 (916 points)
You are really amazing. That's exactly what I am searching for.

Thank you very very much.

Welcome to wellsr Q&A
Ask any questions you have about VBA and Python and our community will help answer them. wellsr Q&A is the standalone question and answer platform for wellsr.com. If you have a question about one of our specific tutorials, please include a link back to the tutorial.

Getting Started
VBA Cheat Sheets (On Sale Now)

Looking for something else? Hire our team directly through ourVBA Help page, instead.

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