Some odd problem with textboxname_BeforeUpdate...

[corentint]

I have been programming a user oriented procedure. Lots of validation are done. I use "before_update" on many textboxes that the user fills.
I was almost finished, but one last textbox procedure with before_update is totally blocking my program: it refuses to fire up when the user (me for the moment) fills in a value.
Symptoms:

1. with a stop on the procedure title line, I can verify that it does not fire up
2. Further, it freezes all other actions attempted, the only one being a command button to exit; there is a reaction to this button being pressed: Excel crashes!
3. I tried changing the name of the textbox, thinking I might have a bad name. I even used textbox100 as a name for it - no dice (textbox1 to 18 are used, of which several have Before_Update events, all working fine)
4. Compiling does not give any error.

Here is the code:

Sub CurRecToDisable_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)                   THE EVENT FOR THIS NEVER FIRES UP
'Validating the GL number entered to find the record to be deleted
Dim RecordS As Double
     Ck = True
    Msg = "Input format must conform:  99999.999, for a total of 9 caracters"
   Msg2 = "Other then the dot, all caracters must be numeric"
'Dot and length format?
    If Mid(CurRecToDisable.Text, 6, 1) <> "." Or Len(CurRecToDisable.Text) <> 9 Then 'Failed
                 CurRecToDisable.Text = CurRecToDisable.Text & " ??"
       CurRecToDisable.BackColor = &HFF&
                                        Cancel = True
                                        XL.Run "MyMsgBox", Msg, 16, "ENTRY ERROR"
                                        Exit Sub
'Numeric only?
ElseIf Not IsNumeric(Left(CurRecToDisable.Text, 5)) Or Not IsNumeric(Right(CurRecToDisable.Text, 3)) Then 'Failed
                 CurRecToDisable.Text = CurRecToDisable.Text & " ??"
       CurRecToDisable.BackColor = &HFF&
                                        Cancel = True
                                        XL.Run "MyMsgBox", Msg2, 16, "ENTRY ERROR"
                                        Exit Sub
End If
       CurRecToDisable.BackColor = &HFFFFFF
                                      RecordS = CDbl(CurRecToDisable.Text)
'In the following Call, variable Ck is for a returned value only
Call GLGLSubFind(RecordS, Ck)                                         'note this suroutine simply checks that the record exists
If Ck = False Then Exit Sub
RecFound.Visible = True
Call Fillform2                                                                      'note this subroutine simply populates all other textboxes on page 2 of the userform
End Sub

=============================================================
A few notes:
A lot of my variables are defined at the module level, some are public for the entire project.
MyMsgBox is an ADDin procedures (an enhanced message box). It works fine in all my coding, including this project
The Exit command button is part of a class that enable one procedure for all exit buttons in my Userforms.
The textbox in question is on page 2 of a multipage userform.

Here are the CurRecToDisable.text properties (only those that I changed):
Name = CurRecToDisable
bckColor = &H8000000B&
Font = Tahoma used throughout all projects
ForeColor = &H8000000D&
SpecialEffect = fmSpecialeffectSunken
TextAlign = fmTextAlignCenter

The textbox is enabled, not locked, visible etc...

What I tried:
Changing the name of the textbox = no dice
Erasing the textbox from the userform, erasing the textbox code from the module and saving the file. Then closing the file, shutting down Excel and restarting Excel. Running all other procedures after this: they all owrk fine. Then I recreate the textbox as Textbox100 and rewrote the code from a text file independant of Excel and Vba. Then I tried again. Still not firing up.

I am at witt's end.

Can anybody give me an idea of was I might be doing wrong?

(I know this entire presentation is a bit long, but I do not want to waste anybody's time, so I told all that I have done so far).

Thank you in advance.

 

[Anthony47]

For what I understand, this event triggers when you exit form the control and the control has been modified.
It could be a problem if, after having modified the textbox content you go to a control, a commandbox for example, that don't take the focus...

 

[corentint]

For what I understand, this event triggers when you exit form the control and the control has been modified.
It could be a problem if, after having modified the textbox content you go to a control, a commandbox for example, that don't take the focus...

I cannot even exit from the control, since it does not fire up meaning the event is not called at all - the procedure is not executed.
I.E. I enter a value in the Textbox, then click enter (as with all my other BeforeUpdate textbox events). The value entered is visible in the control, but absolutely nothing happens (at least on the surface, no error message - just nothing); I can erase my value and retype it or another one, and enter, still nothing, the event does not fire up.
However your inquiry has raised another symptom: I enter a value and click enter. Nothing. So I decide to switch to page 1 of the userform (remember, I am still in the module, even thought nothing has happened). But this time, the simple act of clicking on the page 1 of the userform activates the event that was not working on page 2!!! Seing this, I used this technique to check that once the event fires up, the control did what it was supposed to do: validate the value. It works perfectly, rejecting wrong values, and accepting correct ones.
But it remains that to do this, I have to switch to page 1 to fire the event. This is not kosher.
But it does point to something else! What, however, I still do not know.
Thank you for advancing the progress in solving this, maybe you can do more? What an odd situation!

By the way, I did not mention, but on page 1 there is an almost identical textbox validating the same value in another context (page 1 modify a record, page 2 delete a record). The only difference is that variable names are differents, including that of the textboxname_BeforeUpdate.

 

[Anthony47]

First check that the "EnterKeyBehavior" property of that textobox is set to False.
Then, what happen if you use Tab to get out of the Textbox?

If none of the two tests modify the situation, then check which control should, by design, should get the focus when you press Enter. To see it, in the userform design mode, right-click on the form and select "Tabulation order" (or something similar) and check the sequence; then start the form and check if that "next element" is available or not.

 

[corentint]

First check that the "EnterKeyBehavior" property of that textobox is set to False.
Then, what happen if you use Tab to get out of the Textbox?

If none of the two tests modify the situation, then check which control should, by design, should get the focus when you press Enter. To see it, in the userform design mode, right-click on the form and select "Tabulation order" (or something similar) and check the sequence; then start the form and check if that "next element" is available or not.

Hello,

Yes, the EnterKeyBehavious is set to false. But tab does not get out of this control to the next, for the simple reason that there are no other enabled controls: by that I mean there are five other text boxes but they are NOT enabled, as the user must see but NOT change the information that appears in them once the CurRecToDisable is validated (the culprit textbox). It happens that this page is the first time I have a set with only one user textbox input required, so that all others are filled in.

So I created a new but dummy textbox (not used but enabled) et voilà, problem solved . Alternatively I can program the display textboxes to be enables, but turn them off after the input is validated, of course, except their may still be a nitwit user that will try to enter something in them before doing the first required one = CurRecToDisable (there is a label pointing rather obviously to it but, you know...)

However, why is this hapening in the first place? The beforeUpdate event is designed so that the code can cancel the change of focus (with code which does set cancel = false if the validation fails and not do so if the input validation passes). The use of AfterUpdate would, in this case, also clearly create a similar problem, no?

In any case, it will mark your answer as the one and I thank you for your precious time. Have a great day.

 

[Anthony47]

Thanks for the feedback

However, why is this hapening in the first place? The beforeUpdate event is designed so that the code can cancel the change of focus (with code which does set cancel = false if the validation fails and not do so if the input validation passes). The use of AfterUpdate would, in this case, also clearly create a similar problem, no?

Both the events trigger when you exit the control and the content has been modified. In this case the problem is that "the next" control doesn't take the focus (normally they loop, returning from the last to the first)
You could try and see if the AfterUpdate triggers even if the BeforeUpdate doesn't. In that case, since there is not a Cancel option in the afterupdate event, you need to simulate "cancel" via vba, re-setting the focus to that textbox in case that its content is not formally correct.
Maybe some additional info could come from the theorethical sequence for the controls, that you can see (and modify!) by rightclicking onto the userform when in the userform developer window.

Or you stay with the "invisible dummy textbox"; because not all the odd behaviours have an explanation, and sometime we need to accept that "it is that way"...

 

[corentint]

Thanks for the feedback

Both the events trigger when you exit the control and the content has been modified. In this case the problem is that "the next" control doesn't take the focus (normally they loop, returning from the last to the first)
You could try and see if the AfterUpdate triggers even if the BeforeUpdate doesn't. In that case, since there is not a Cancel option in the afterupdate event, you need to simulate "cancel" via vba, re-setting the focus to that textbox in case that its content is not formally correct.
Maybe some additional info could come from the theorethical sequence for the controls, that you can see (and modify!) by rightclicking onto the userform when in the userform developer window.

Or you stay with the "invisible dummy textbox"; because not all the odd behaviours have an explanation, and sometime we need to accept that "it is that way"...

So it is the way. I am getting wiser too, I hope