Loop through controls on a userform not triggering afterupdate event

[BCVolkert]

I have a form in excel that includes a large number of controls. For many of the controls, there is an _AfterUpdate event that fires correctly when the user enters data. The _AfterUpdate code makes entries in one or more ranges in the associated spreadsheet (I am not using the ControlSource properties for these controls). Calculations are performed correctly in the spreadsheet on the basis of the changed values. Finally, at the end of each of these _AfterUpdate routines there is a call to a frmMealPlanAdditionsUpdate procedure to repopulate the controls on the form based on the current values of various ranges in the spreadsheet and the results show up in the form correctly as the user continues working. So far, so good.

I have included a Clear button on the form that has a _Click event for which the code is included below. This works to the point of clearing the contents of the form. When using the form, all the controls are cleared when the Clear button is clicked. However, the associated ranges in the spreadsheet have not been affected (I can see them on the screen while the form is visible).

Apparently, the _AfterUpdate events are not being triggered. I have checked this by including breaks in the code below and in the associated _AfterUpdate procedures. I also tried a variety of ways to call the _AfterUpdate directly, but have not found anything that gets past the debugger when executing. In this respect, the behavior is the same for text boxes and combo boxes. I have also tried changing to _Change; but, that led to a bunch of other problems and I came back to using _AfterUpdate.

The code below for is in the module for the form (i.e. the one that shows up when I double click a control in the VBE). I also included the code for one of the _AfterUpdate procedures. The code for the frmMealPlanAdditionsUpdate procedure is rather long, so I just included the line that is affected by the txtFoodGramsCarbohydrate_AfterUpdate procedure as an example.

I must be missing something fundamental because various forms of the approach to looping through all the controls have been posted in lots of places for years. Nobody complains that the values come back. In my case, I want some of the values on the form to be a function of other values that have been entered. In general, the form does this well when I use the form manually. I can even clear the controls manually and the _AfterUpdate procedures are called. However, changing the values for the controls using the btnClear_Click does not. I suspect that making the changes using VBA in another context will not trigger the _AfterUpdate either.

I been at this for a while now and I'm out of clues. Any help will be appreciated.

Bruce

Private Sub btnClear_Click()

    'Clears all of the controls on the form that the user can change. 

    Dim ctl As MSForms.Control ' was just "contol" instead of "MSForms.Control"
    
    For Each ctl In Me.Controls
        Select Case TypeName(ctl)
            Case "TextBox"
                ctl.Text = ""
            Case "CheckBox", "OptionButton", "ToggleButton"
                ctl.value = False
            Case "ComboBox", "ListBox"
                ctl.ListIndex = -1
        End Select
    Next ctl

End Sub


Private Sub txtFoodGramsCarbohydrate_AfterUpdate()

    If txtFoodGramsCarbohydrate.Text = vbNullString Then
        Range("FoodGramsCarbohydrateInput").value = txtFoodGramsCarbohydrate.Text
    Else
        Range("FoodGramsCarbohydrateInput").value = CDbl(txtFoodGramsCarbohydrate.Text)
    End If
    
    Call frmMealPlanAdditionsUpdate
    
End Sub


Public Sub frmMealPlanAdditionsUpdate()


' Transfers information to the form.

'  Do stuff with all the form data.

    txtFoodGramsCarbohydrate.Text = Format(CStr(Range("FoodGramsCarbohydrateInput").value), "0.00")

' Do more stuff with all the form data. 
    
End Sub

 

[JLGWhiz]

If you have not tried using the F8 (or Debug tool bar) key to step through the code then try this.
1. Set a breakpoint on your btnClear-Click macro.
2. Click the button
3. Diminish your vbEditor screen by opening and resizing so you can see your worksheet, UserForm and code simultaneously.
4. Use the F8 or Debug Toolbar to step through the code one line at a time.
5. As each line of code executes, check your sheet and userform to see if the code is doing what you expect.
6. Repeat this process for the related macros, if necessary.
You can hover the mouse pointer to see certain values of variables and functions in the tool tips box as you execute the code. This is a debugging procedure that I use for complex code to make sure the process is following the path that I expect it to follow and that values are what I expect them to be.

 

[BCVolkert]

Thanks for your response.

I'm familiar with the process. I use two monitors and its pretty easy to see what is going on. I hover over the various variables and can see the values. Everything is as expected. What is perplexing is that changing the ctl.text property does not trigger the _AfterUpdte event and the loop moving to the next ctl does not trigger the AfterUpdate event.

Just to be sure, I inserted breakpoints in the btnClear_Click routine and in one of the _AfterUpdate routines. I started the form. As intended, existing values populate the form as the Update routine executed. Clicking the Clear button triggered the btnClear_Click routine and it steps through all the controls in turn, never triggering the AfterUpdate event. When the btnClear_Click routine finishes, the form controls are cleared; but the associated ranges in the spreadsheet were not changed by the _AfterUpdate routines. Continuing with the form that remained active, when I change the control having the breakpoint in the AfterUpdate routine, the AfterUpdate routine does trigger and it functions properly. However, all the values in the other fields are then filled based on the information in the associated ranges (not via ControlSource) by the actions of the Update routine. This confirms that the btnClear_Click routine was firing, that the Update routine functions consistently with the values in the ranges, and that the _AfterUpdate routine works if it would get triggered.

If I rename the _AfterUpdate procedure to be a _Change procedure, the _Change procedure does run as the btnClear_Click procedure runs and encounters the ctl having the _Change procedure. However, in the context of simply using the form, the _Change procedure does not play well with the _KeyPress event that I'm using to assure only numbers are entered or the tab key when trying to move around in the form. I seem to recall other horrors associated with the _Change procedure; so, I'm a little reluctant to go down that route unless I can tame some of its other bad habits.

So, what I really, really want is that I have a means to clear the controls and have the results of that clearing process be carried into the ranges on the spreadsheet that I intend to change. My alternative at this point may be to explicitly call all of the AfterUpdate routines in the btnClear_Click routine. That seems like a hack to me and something that would not result in reusable code.

 

[JLGWhiz]

Does Excel even have the AfterUpdate event in its libraries, or is that Access only? I didn't see it in the UserForm declarations window. But that don't always mean a lot.

 

[BCVolkert]

Although I don't really know what a library is, I suppose Excel does have _AfterUpdate in its libraries. I get to this inference because when I put a control on the form, and double click it, I get an opportunity to create the routines having a variety of _xxxxx endings. The default seems to be _Change; but picking any of the others in the dropdown list at the right of the VBE creates a stub of code that you can begin with. Changing the text of the ending causes the behavior to change predictably (in a Microsoft sort of way) that indicates to me that that is all that is required to morph one sort of event into another.

I'm still stuck and the hack of just replacing the loop with a bunch of hard-wired coding is looking better. Yuk!

 

[JLGWhiz]

Although I don't really know what a library is, I suppose Excel does have _AfterUpdate in its libraries. I get to this inference because when I put a control on the form, and double click it, I get an opportunity to create the routines having a variety of _xxxxx endings. The default seems to be _Change; but picking any of the others in the dropdown list at the right of the VBE creates a stub of code that you can begin with. Changing the text of the ending causes the behavior to change predictably (in a Microsoft sort of way) that indicates to me that that is all that is required to morph one sort of event into another.

I'm still stuck and the hack of just replacing the loop with a bunch of hard-wired coding is looking better. Yuk!

Re: Library. I was referring to the .dll files (Dynamic Link Libraries) that contain the application software that makes things like 'AfterUpdate' work. I can't find any reference to AfterUpdate in the Excel VBA language reference, but it does have references to Access and Outlook. So that could be why it is not triggering. Have you tried the Deactivate event, or would that not fit into your game plan.

 

[BCVolkert]

Thanks for pointing out the libraries. I looked online; but, I suspect you are referring to those listed in the Object Browser within the VBE. I checked Library(MSForms) / TextBox / Members of TextBox and it does not list all of the Procedures for which there are choices in the VBE. These extra choices in the VBE include AfterUpdate, BeforeUpdate, Enter, and Exit. That's where I got the idea to use AfterUpdate. These choices are available to me in the dropdown box on the upper right of the VBE when I'm in a region of the code associated with a TextBox newly placed on the form.

In my form, the AfterUpdate event does work; so its not something I dreamed up and just typed in. The code stub was placed there by the VBE when I selected AfterUpdate from the dropdown in the Excel VBE. I filled in the code to update the associated ranges and they all work when I update the respective controls while using the form with a mouse and keyboard. The AfterUpdate event just does not trigger while the code for the Clear button runs through all the controls and changes the values to "", -1, or False. as indicated in the Select Case portion of the code.

I'm just guessing here; but, I suspect AfterUpdate is not triggering because changing the value of the .Text property is not considered to be an "Update" but is considered to be a "Change". Altering my approach to triggering to a Change event has not been an acceptable solution to me because (having tried that), it does not seem to be compatible with using a KeyPress event to check for invalid characters and the desire to allow the user to tab from TabStop to TabStop while using the form. The KeyPress routines do work in response to keys and it is fine that they are not fired by the Clear button's code. I think the problem there is that each key constitutes a change, triggering the Change event and that has included a call to the Update routine that picks up all the old values again.

You may be onto something with there being some sort of different handling of the AfterUpdate and Change events in the documentation. AfterUpdate, BeforeUpdate, Enter, and Exit may be some sort of second class Procedures inherited from a superior class; but, that's just a guess.

So . . . . I'm going to implement the hard-coded hack to call a routine to make changes to the ranges directly when my Clear button is clicked. I suspect that will be ugly; but, I'll be able to move on.

Thanks for your time and attention.

 

[JLGWhiz]

I was able to finally get the technical description of the event, thanks to your explanation of where you found the access to it. I have never used it before and do not, at this point, quite understand how the event works. Here is what the help file gives.

The AfterUpdate event occurs regardless of whether the control is bound (that is, when the RowSource property specifies a data source for the control). This event cannot be canceled. If you want to cancel the update (to restore the previous value of the control), use the BeforeUpdate event and set the Cancel argument to True.
The AfterUpdate event occurs after the BeforeUpdate event and before the Exit event for the current control and before the Enter event for the next control in the tab order.

I will have to work with it a while to fully understand what it is doing. So for now, I can't offer any solution to your problem.

 

[BCVolkert]

I appreciate your persistence.

I decided to make lemonade. Instead of just setting everything to NullString etc., I set up some default values for all of the ranges of interest (also in ranges in the spreadsheet). Now the Clear button sets all the ranges to these default values after which the Update routine sets the values of all the controls to these default values. That leaves me a lot of latitude to make the Clear button more of a Reset to the defaults and I can control the defaults with a lot of specificity.

In the end, this is probably better for the user because I have some values that need to be something other than nothing (e.g. tolerances, desired resolution of the answer, increment to be used hunting for an optimum, etc.). Those things are not computing concepts; but, they are relevant to this particular application.

I'm still really curious as to why Methods show up in the VBE dropdown that are not present in the documentation for the MSForms / TextBox provided by the Object Browser. The information presented in the Object Browser does seem to be automatically generated because my own objects (e.g. VBA Routines that I wrote) show up there. It is at least plausible the list of Events in the VBE is also generated through some sort of inheritance or other process that is not completely consistent with the approach taken to generate the less complete list in the Object Browser. Maybe some MVP will provide some insight.

Meanwhile, I'm moving forward with my "improved functionality".

Bruce

 

[JLGWhiz]

I have noticed that the Microsoft quality control is not the best. It could be that VBA is a low priority for them and updating all the various peripheral files that are associated with VBA is something they might do on a time available basis. A couple of years back they issued an update that wiped out the Active-X controls and crippled all existing programs that used them. That is why the issued Windows 10 gratis to all legitimate Windows users. There was a time when I relied heavily on the VBA help file to write macros before the turn of the century. Today, I get a lot of my info right off the web from various forums and some of the MVP web sites. Glad you can work around the problem.
Regards, JLG