Errorhandling code which operates at Form level - professional approach

[Greg M]

Any professional Excel developers out there???

I'm trying to see if it's possible to reduce the amount of code contained in the VBA codemodule of a form while still including sufficient errorhandling code to prevent the user being presented with a generic VB error message.

To illustrate what I mean, I have a simple form (F01_ClientDetails) which contains a single control (btnCancel), and whose codemodule contains the following code:

Option Explicit


Private Sub btnCancel_Click()

    Me.Hide

'   Deliberately generate an error
'    MsgBox 1 / 0

End Sub

The routine which displays the form is as follows and contains errorhandling code:

Option Explicit


Sub ShowForm_ClientDetails()

    Dim frm As F01_ClientDetails

    On Error GoTo ErrorEncountered

    Set frm = New F01_ClientDetails

        With frm

            .Show

'           Deliberately generate an error
            MsgBox 1 / 0

        End With

    Unload frm

    Set frm = Nothing

ExitPoint:

    Exit Sub

ErrorEncountered:

    MsgBox "An error was encountered in routine ""ShowForm_ClientDetails""", _
            vbExclamation

    Resume ExitPoint

End Sub

When the form is closed via the "btnCancel_Click" routine, the custom error message is displayed when the "Msgbox 1 / 0" statement in the "ShowForm_ClientDetails" routine is executed.

So far so good, BUT, once the form has been displayed by the Show method, the above errorhandling is not active until control is returned to the "ShowForm_ClientDetails" routine. This means that a "nasty" generic error message will be generated if an error is encountered while code in the VBA module of the form is running - e.g. if the comment quote is removed from the "Msgbox 1 / 0" statement in the "btnCancel_Click" routine.

It's a relatively simple matter to include appropriate errorhandling code in the "btnCancel_Click" routine, but what happens if the form contains several control buttons, several textboxes with "BeforeUpdate" and "AfterUpdate" routines, and several comboboxes with "Change" routines?

Is it necessary to provide individual errorhandling code in each of the above routines or can some sort of common code be provided only once at the form level?

It's not essential for errorhandling code to specify the exact routine in which the error occurred - what's important is that a custom error message be displayed rather than a generic VB error message. Also, it is not necessary for the errorhandling code to perform any "clean up" operations, it just needs to display the custom message.

Any comments or suggestions would be much appeciated.

Regards,

Greg M