Some odd problem with textboxname_BeforeUpdate...

corentint

New Member
Joined
Jan 31, 2022
Messages
28
Office Version
  1. 365
Platform
  1. Windows
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:

VBA 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.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
3,285
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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...
 
Upvote 0

corentint

New Member
Joined
Jan 31, 2022
Messages
28
Office Version
  1. 365
Platform
  1. Windows
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.
 
Upvote 0

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
3,285
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 
Upvote 0
Solution

corentint

New Member
Joined
Jan 31, 2022
Messages
28
Office Version
  1. 365
Platform
  1. Windows
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 :cautious: (there is a label pointing rather obviously to it but, you know...)

However, why is this hapening in the first place? :unsure: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.
 
Upvote 0

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
3,285
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Thanks for the feedback
However, why is this hapening in the first place? :unsure: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"...
 
Upvote 0

corentint

New Member
Joined
Jan 31, 2022
Messages
28
Office Version
  1. 365
Platform
  1. Windows
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(y)
 
Upvote 0

Forum statistics

Threads
1,186,658
Messages
5,959,039
Members
438,394
Latest member
jagruti

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top