General error handler: If any error in UserForm other than msgBox: Err.Description

Question

As soon as I get an error in my code, I want a msgbox to open. I have intentionally built errors into my code, but a msgbox does not appear, MicroSoft Visual Basic for Applications opens.

How can I get my msgbox to appear? Also, how can I also use the query for complete UserForm? There is the UserForm_Error function, but is it meant for that?

The intentional error is that there is no DataBaseTest, which I want to trigger this error:

Error: variable not defined

Private Sub ConnectCheck()
    ' Versuchen, eine Verbindung zur Datenbank herzustellen
    On Error GoTo ErrorHandler
    Dim connStr As String
    connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & PathDB & ";Jet OLEDB:DatabaseTest Password=" & PasswordDB & ";" 'no DatabaseTest intentionally entered a mistake
    
    Dim conn As Object
    Set conn = CreateObject("ADODB.Connection")
    

    conn.Open connStr

    
    If conn.State = 1 Then
        ' Verbunden
        LabelConnect.Caption = "Connected"
        LabelConnect.Font = RGB(0, 255, 0)
    Else

'        LabelConnect.Caption = "Not Connected"
'        LabelConnect.Font = RGB(255, 0, 0)
    End If
    

    conn.Close
    Set conn = Nothing
    Exit Sub
ErrorHandler:
    MsgBox "Error: " & Err.Description
    

End Sub

Answer 1

It looks like you may be referring to a compile-time error. However, the On Error statement only handles a run-time error. So you should manually compile your code before trying to run it. This way you'll avoid a compile-time error when running it.

First, make sure that you have the following statement at the very top of your module before any procedures. This will force you to declare all variables. So, if you try to use an undeclared variable, you'll get a compile-time error.

Option Explicit

Then manually compile your code, and fix any errors that pop-up. Once any errors are fixed and your workbook is saved, any errors that pop-up will occur a run-time, which your On Error statement will catch.

Visual Basic Editor (Alt+F11) >> Debug >> Compile VBAProject