VBA Multiple Textbox Exit Event Question

[Annie Bender]

Hi guys. Been a while since I needed your help, but I'm back again. The project: Form with lots of textbox controls (dynamic, up to ~150). I want to do validation upon exiting these controls, but don't want to have a separate Textbox_Exit sub for each one of them to call the identical validation test (contained in one validation sub). How can a single "generic" Textbox_Exit sub be written to apply to each of the 150+/- controls that require validation? Is this where a class comes in? If so, that's new programming to me, so some guidance would really be appreciated. Actually, however it has to be done, I need your help. Thanks as always for straightening me out.

--Annie in Florida

 

[ZVI]

@ZVI: Are you saying that the Exit event doesn't work in a class module at all? In other words, there is no way I can adapt Chris' example as long as I am trying to use an Exit event for a TextBox?

Annie, for regret Exit event doesn't work in a class module for Texbox object at all.

You may easily check this fact in this way:
1. Add class module to the project with UserForm
2. Put into Class1 module the code: Public WithEvents MyTxtBox As MSForms.TextBox
3. You can find two comboboxes above – left side one with default (General) value and another right side combobox with (Declarations) default value.
Choose MyTxtBox item in right side combobox - Private Sub MyTxtBox_Change() template is auto added to the Class1 module.
4. After that open the right side combobox with Change value and find all supported events for MyTextBox.
You can’t find Enter nor Exit events.

As alternative you can check all texboxes contents before quitting the form in UserForm_QueryClose subroutine or in Apply/Ok button code.

Regards,

 

[RoryA]

See this thread, particularly Jaafar's later posts.

 

[ChrisM]

Well ZVI understands this stuff much better than me! I poked around following his directions and can't come up with any workarounds. Every thought I had involved calling a bit of code from each of the 150 textboxes, which is exactly what you are trying to avoid.

Here is my only idea. The click event DOES work. You could set it up so that every time the user clicks a textbox, the class module forces an error check of every textbox (because the assumption is they just left another textbox, but you have no way of knowing which one).

Obviously this could be resource intensive, you'd have to test it out and weigh against how often they would be clicking in the text boxes.

You would still want/need to run a final test when they click the submit button.

FYI, the Change event is also exposed, but that fires after every key press, so not usable for you.

 

[Annie Bender]

Thanks Rory. This is a giant learning exercise for me, and Jafaar's posts are taking me to school, for sure. I foresee some trial and error in my future with this, but I'm looking forward to the challenge. Thanks for directing me to the link.

BTW, why are the Enter/Exit events not supported in a class module for Textboxes in VBA?

 

[RoryA]

Those events actually belong to the container control on the form, not the textbox itself. Unfortunately you can't use an MSForms.Control WithEvents.
And trust me, Jaafar's posts are usually an education for everyone - I've learned a lot from them!

 

[Annie Bender]

Hi all. I'm up against a brick wall. I can follow Jafaar's coding for the Enter/Exit class, but when I adapt it to my purpose, it triggers the Exit event only on exiting the user form itself, not when exiting a textbox on the form. Have I misunderstood his example all along, or was he indeed suggesting a workaround for the Exit event when a textbox is the control to check? Every modification I can think of to set the active form control to the target textbox rather than to the userform itself, results in either a compile or run-time error. I'm out of ideas, guys.

Here are the relevant portions of my attempted coding. Is it real obvious what I'm doing wrong?

The custom class module (named clsWatchEvents)

Option Explicit

Public WithEvents FormCtrl As UserForm12B
Private bFormUnloaded As Boolean
Private bCancel As Boolean
Private oPrevActiveCtl As MSForms.Control

Private Sub FormCtrl_OnEnter(Ctrl As MSForms.Control)
    ' do nothing
End Sub

Private Sub FormCtrl_OnExit(Ctrl As MSForms.Control, Cancel As Boolean)
    
    Select Case True
        Case Ctrl Is Me.TextBox7
            If Not Ctrl.Text = arrParts(1, 7) Then
                Cancel = True
                Ctrl.BackColor = &H80000005
                MsgBox "The installation date has changed. Do you wish to adjust the inventory?"
            End If
'       Case '.......
    End Select

End Sub

The userform (named UserForm12B)

Option Explicit

Public Event OnEnter(Ctrl As MSForms.Control)
Public Event OnExit(Ctrl As MSForms.Control, Cancel As Boolean)

Private oXitClass As clsWatchEvents
Private bFormUnloaded As Boolean
Private bCancel As Boolean
Private oPrevActiveCtl As MSForms.Control
Private oCol As New Collection

Private Sub UserForm_Layout()
    Call WatchEvents
End Sub

Private Sub UserForm_Terminate()
    Call CleanUp
End Sub

Private Sub WatchEvents()
    If Not oXitClass Is Nothing Then Exit Sub
    Set oXitClass = New clsWatchEvents
    Set oXitClass.FormCtrl = Me
    bFormUnloaded = False
    Set oPrevActiveCtl = Me.ActiveControl
    RaiseEvent OnEnter(Me.ActiveControl)
    
    Do While bFormUnloaded = False
        If Not oPrevActiveCtl Is Nothing Then
            If Not oPrevActiveCtl Is Me.ActiveControl Then
                RaiseEvent OnExit(oPrevActiveCtl, bCancel)
                RaiseEvent OnEnter(Me.ActiveControl)
                If bCancel Then
                    oPrevActiveCtl.SetFocus
                Else
                    Me.ActiveControl.SetFocus
                End If
            End If
        End If
        Set oPrevActiveCtl = Me.ActiveControl
        DoEvents
    Loop
End Sub

Private Sub CleanUp()
    bFormUnloaded = True
    RaiseEvent OnExit(oPrevActiveCtl, bCancel)
    Set oXitClass = Nothing
    Set oCol = Nothing
    Set oPrevActiveCtl = Nothing
End Sub

Private Sub UserForm_Initialize()
    ' loads worksheet data into an array for later processing and into all of the
    ' countless textboxes that make up the form, some of which are the reason for wanting
    ' to check the exit event.
End Sub

Where do I go from here? Thanks for reading through this.

--Annie

 

[Annie Bender]

Oh, I am so stumped! There's something here I'm overlooking, because I just can't make it recognize the textbox exit event. I've read everything I can find on this, and I don't know where else to turn. Hasn't anyone done this successfully who could point out where I'm missing the boat? Thanks so much guys.

--Annie

 

[Annie Bender]

Bump. I'm still looking for guidance here. Please help me if you can. Anyone?

 

[ZVI]

Annie, could you please post the code for only one textbox without using of class module?
Or just tell what data type is expected in the textboxes.
Vlad

 

[Annie Bender]

Hi Vlad. The code is simply to test whether the string value entered by the user into a textbox differs from the value of that variable as saved in an array at the time of form initialization. If the values are different, a msgbox is displayed for further action on the part of the user. If there were only a few such textboxes to test on exit, I would simply make a Textbox_Exit sub for each one. But there are about 150 of them. The coding is:

If Not Ctrl.Text = arrParts(1, 7) Then
     Cancel = True
     Ctrl.BackColor = &H80000005
     MsgBox "The installation date has changed. Do you wish to adjust the inventory?"
End If

Where Ctrl is the specific textbox being tested. Thanks for your help.

--Annie