Stopping "TextBox_Change" event from executing if called by code

Question

I have a userform which contains a TextBox object named myTextBox.

The text inside the object can be changed either by the user or by the code. There's a "onChange" procedures attached to the textbox:

Private Sub myTextBox_Change()
    'do some stuffs
End Sub

I would like the event to be processed only when is the user changing the text, but not the code. I had thought about adding an Optional parameter like the following:

Private Sub myTextBox_Change(Optional isCode As Boolean)
    If isCode = False Then
        'do some stuffs
    End If
End Sub

but this is not really helpful because I cannot pass the isCode = True when changing the name programmatically, like this:

myForm.myTextBox = "new text"

Does anyone have an idea on how I can fix this?

GavinP · Accepted Answer · 2015-01-23 11:46:12Z

1

One way (perhaps not the right way?) would be to declare a global variable to use in the same way as your IsCode Boolean. Set it to true during your code block so the textbox_change event knows code is processing in the background, and then set back to false when that code has finished.

Put this at the top of a normal module (won't work in a form module)

Option Compare Database
Option Explicit
Global IsCode As Boolean

and then in the code you're running just set IsCode=true as required.

answered Jan 23, 2015 at 11:46

GavinP

6871 gold badge6 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Matteo NNZ Over a year ago

Not a bad idea, I had also thought about a property of the form isCode to set True or False depending on if I want the event handler to be fired, but will wait a bit more to see if someone knows some way to deactivate the event-handler in VBA (it would be cleaner I think).

Matteo NNZ Over a year ago

Finally going to use your solution, I preferred not to overload the form with useless properties and nobody has told me yet about how to remove the change listener! Thank you

Collectives™ on Stack Overflow

Stopping "TextBox_Change" event from executing if called by code

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related