VBA Excel 2010: error 438 on exiting last text box in frame

[Andyatwork]

Hello all,

This is a problem I think I've encountered before but cannot remember the solution to.

Situation:
I have three text boxes in a Frame, on a form. The user enters date values in each text box and clicks a button. The code is supposed to check the entered text, swap out "." for "/", confirm the text is a date and write the values to a spreadsheet.

The problem:
The code works fine on the first two text boxes but when it exits the third and final box I suddenly get an error 438 and Excel crashes when I End the code.

The irritation:
This is recycled code from another project which does exactly the same thing but works without falling over or crashing the application.

The code:

Private Sub txtVoteLive_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Call CreateNewProxy.test_date_format
End Sub

This is in the form and similar code is attached to each text box_BeforeUdpate differing only in the name of the text box.

Sub test_date_format()

    Dim ctrltext As String
    ctrltext = frmCreateNewProxy.ActiveControl.ActiveControl.Text
    
    If ctrltext <> "" Then
        If InStr(ctrltext, ".") <> 0 Then
            ctrltext = Format(Replace(ctrltext, ".", "/"), "dd/mm/yyyy")
        Else: End If
        ctrltext = Format(ctrltext, "dd/mm/yyyy")
    Else: End If

    frmCreateNewProxy.ActiveControl.ActiveControl.Text = ctrltext

End Sub

This is in a module and is called by the before_update line attached to the text box.

I don't have anything attached to the Frame or the Form.

Any ideas why it would decide to fall over on exiting the last text box / Frame?

Many thanks,

Andy

 

[Andyatwork]

OK, done some googling and it turns out this is a known bug.

If a textbox is the last control in a frame, then the "on_exit" or "BeforeUpdate" event will not trigger. A workaround is to crowbar some other control into the frame so that it becomes the last control in the frame and make that control invisible, or take the text boxes out of the frame.