Run a macro if any of textboxes change - vba

[kelly mort]

I have about 50 textboxes that I want to run a certain macro each time their contents change.
I was doing it individually - is there a way I could loop through the textboxes with a function or anything similar?

The textboxes are on a userform
Thanks in advance

 

[Jon Peltier]

Putt a break point at the Call MyMacro line, then start the code that loads the form. Does execution stop at the break point?

 

[kelly mort]

Putt a break point at the Call MyMacro line, then start the coffee that loads the form. Does execution stop at the break point?

How do I put the break point?

 

[Jon Peltier]

Click on the far left margin of the code window. The line of code will be highlighted in a dark maroon or brown color and there will be a circle of the same color in the margin. The code will pause when it gets to the breakpoint. You can then step through using the F8 key to see where the code goes.

 

[Logit]

You did change "MyMacro" to the real name of your macro ?

 

[kelly mort]

Yes

You did change "MyMacro" to the real name of your macro ?

Yes I did change it.

It is working now for the initialize event but not for when I change content of the textboxes

 

[Logit]

Here is a portion of the code you are seeking. Review the entire code in the download workbook (ThisWorkbook module - UserForm code - Class Module).

Public ctl_collection As New Collection
Private Sub Userform_Initialize()
    For Each ct In Controls
        If TypeName(ct) = "TextBox" Then
            ctl_collection.Add New inputcheck, ct.Name
            Set ctl_collection(ct.Name).cl_textbox = ct
        End If
    Next
End Sub

Download example workbook : Test.xlsm

 

[kelly mort]

Here is a portion of the code you are seeking. Review the entire code in the download workbook (ThisWorkbook module - UserForm code - Class Module).

Public ctl_collection As New Collection
Private Sub Userform_Initialize()
    For Each ct In Controls
        If TypeName(ct) = "TextBox" Then
            ctl_collection.Add New inputcheck, ct.Name
            Set ctl_collection(ct.Name).cl_textbox = ct
        End If
    Next
End Sub

Download example workbook : Test.xlsm

Wow - I can't believe my eyes!

Thanks for the code.

My textboxes are number like this txt1 txt2 and so on.

Is there a way to let the code respond to just txt4 to txt50?

 

[Jon Peltier]

The code will not run when a text box changes, because it's not in a TextBox_Change event procedure.

Instead of the UserForm_Initialize procedure, put the call to your macro in the UserForm_MouseMove procedure. Now it will be called whenever the mouse moves over the form.

 

[Jon Peltier]

"Is there a way to let the code respond to just txt4 to txt50?"

One way:

Dim iTxt as long
iTxt = Val(Mid$(ctrl.name, 4)
If iTxt >= 4 And iTxt <= 50 Then
  ' do your stuff
End If

Another way: Put a descriptive string into the Tag property of the textboxes you want to test. Then

If ctrl.Tag = "My String" Then
  '' do your stuff
End If

 

[Gixxer Lad]

Here is a portion of the code you are seeking. Review the entire code in the download workbook (ThisWorkbook module - UserForm code - Class Module).

Public ctl_collection As New Collection
Private Sub Userform_Initialize()
    For Each ct In Controls
        If TypeName(ct) = "TextBox" Then
            ctl_collection.Add New inputcheck, ct.Name
            Set ctl_collection(ct.Name).cl_textbox = ct
        End If
    Next
End Sub

Download example workbook : Test.xlsm

Good evening