When to use afterupdate rather than exit event

[Michael Simpson]

Thought I'd start this topic since there seem to be a number of topics where the answer seems to be to use one of the above rather than other. Thought I'd kick off with my 2 cents' worth.

I have a userform with frames containing textboxes. The user enters a currency value and once they leave the control, then a protected textbox next to it shows the corresponding value in SEK. I started off using the exit event but ran into 2 problems.

1. If you tabbed out of the last textbox in the frame, the exit event never kicked in (this is documented in other topics but took some time to find). This resulted in me using the exit event for all except the last textbox in the frame that used afterupdate instead
2. I then discovered that the exit events didn't kick in if, instead of tabbing out of the field, I deliberately placed focus in a control elsewhere on the form. Changing the event from exit to afterupdate corrected this.

My question then is ... could you guys document in this topic when you would/must use the exit rather than the afterupdate event (or vice-versa).

Thanks

 

[Michael Simpson]

Thank you for your patience Andrew.

Your example works fine (as did mine). The problem is the fact that setting cancel to true on the frame exit event doesn't seem to place focus anywhere visible (I'm guessing that focus is placed on the actual frame or userform ?????)

 

[Andrew Poulsom]

For me the focus was placed on the control that was exited.

 

[Michael Simpson]

Aha !!!! Maybe we're getting somewhere. What version of Excel are you running ? I'm on 2007 (how can I see what fixpack or level I'm on ?).

Could I send you a stripped-down version of my excel application to check if our versions work differently ?

 

[Michael Simpson]

In fact I just created an example, and there it works fine. I'm starting to wonder if there's some sort of a bug with 2003==>2007 conversions since the original application was created using Office 2003.

Is there a simple way of exporting everything from a workbook (I mean data, sheets, userforms etc etc) and then importing it to a new workbook ?

 

[Andrew Poulsom]

I was using Excel 2003 when I wrote and tested the code. But it also works for me in Excel 2007 (12.0.6545.5000 SP2 12.0.6545.5004). I got that from office Button|Excel Options|Resources.

You can copy the sheets to a new workbook, and you can export/import your code.

 

[Michael Simpson]

Weirder and weirder. The first control I have in my userform is a combo box called country_combo. This contains the following code

Private Sub country_combo_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    Dim Rng As Range
    Dim config As Integer, LR As Integer
    Dim ans As String

    If WorksheetFunction.Trim(Country_combo) <> "" Then
        With Sheets(2).Range("A:A")
            Set Rng = .Find(What:=Country_combo, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                ' The strange condition above is when it's found
                ' Application.Goto Rng, True
                Cancel = False      ' Allow the code to leave the control
            Else
                config = vbYesNo + vbQuestion + vbDefaultButton2
                ans = MsgBox(Country_combo & " är ej definierad ännu - vill du lägga till det som ett nytt land", config)
                If ans = vbNo Then
                    Cancel = True
                    Exit Sub
                End If
                ' They want to add it as a nw country. This means we need to do 2 things:-
                ' - add it as a country on sheet 2
                ' - add it to the combo box
                Sheets(2).Activate
                LR = 1 + ThisWorkbook.LastRow("A")
                Sheets(2).Cells(LR, "A") = Country_combo
                Call fill_country_combo
                Sheets(1).Activate
            End If
        End With
    End If

End Sub

As can be seen (?) with this code, if the user enters a country that isn't already defined in a specific column in another sheet, then they are asked if they REALLY want to to define this new country. If they answer No, the code does the CANCEL = True and exits the sub.

Not even this results in focus being visible in the country combo, so I'm left wondering whether there's something weird with my cancel definition (yes, I know that sounds ridiculous) or focus is being placed somewhere unexpected/invisible (like the actual userform ???)

Is there a way of tracing the events tied to a user form ?

 

[Andrew Poulsom]

You can put a break point in your code and step through it to see what's happening.

 

[Michael Simpson]

You can put a break point in your code and step through it to see what's happening.

I'm doing that already. I just wondered if there was some way of tracing any/all events that Excel runs as I do this and that on the userform.

At the moment, I'm going through the tedious business of creating an "identical" userform and running it bit by bit to see if/when it suddenly won't honor the cancel=true command in the exit event. At the moment, everything is working as expected (ie, when I suddenly can't see focus being placed back in the control I just left).

I'll update this topic when I'm either finished (and everything works as expected) or when I hit the specific "thing" that stops the exit event working properly

 

[Michael Simpson]

After a load of redefining/copying, I've finally (?) arrived at the difference/problem. If I start the useform via F5 from the userform VBE panel, then all focus works correctly.
If instead, I start the userform via the code

Private Sub agenda_data()
    Agenda_data_entry.Show vbModeless
End Sub

(I have added various menu options, so this this code is run via an add-in) then the cursor "disappears".

Note that the actual userform contains both an initialize and an activate event. Both are run irrespective of which method I choose to bring up the userform.

I've put together a simple test case - may I send it to you Andrew ?