Can't catch error from Cancel button on Common Dialog Box

I have searched for the answer to this but the typical response to catch when a common dialog box is shown with the showsave or showopen and the user hits cancel it ignores my on error goto statement. It throws the error and the project goto debug mode.

On Error GoTo ErrHandler
CommonDialog1.CancelError = True
CommonDialog1.DialogTitle = MSG66
CommonDialog1.FilterIndex = 1
CommonDialog1.DefaultExt = "xlsx"
CommonDialog1.Filter = "Excel (*.xlsx)|*.xlsx|(*.xls)|*.xls|All Files (*.*)|*.*"
CommonDialog1.InitDir = path
CommonDialog1.FileName = path & "AuditorRecap"
'CommonDialog1.CancelError = True
CommonDialog1.FLAGS = FileOpenConstants.cdlOFNOverwritePrompt + FileOpenConstants.cdlOFNHideReadOnly
'Show the dialog box
CommonDialog1.ShowSave -> user hits cancel and project throws the prompt with Run-time error 32755, cancel was selected with the End and Debug buttons. Hit debug and it is on this line

I have noted that the past posts on this were using the Common Dialog Box with SP3. I am on SP6 and went back to SP3 Common Dialog Box and that made no difference.

Any clues?

Right-click any code window, select Toggle-->Break on Class Module, which is the default. You probably selected "Break on All Errors" by accident. That option makes the IDE ignores On Error statements(in the IDE only, not in the EXE). It's useful when you have a big program, and want to show all errors that some maybe hidden without commenting any On Error. This article explains what the options do:

https://jeffpar.github.io/kbarchive/kb/129/Q129876/

That worked... Thanks a lot

I would like to draw your attention to the fact that using the Common Dialog Box SPx is the wrong strategy. Why connect an additional OCX to the project if you can do with a small module and a fairly small code, but with your own code, with a support for unicode file names. The OCX for file selection does not support unicode files and causes your project to be dependent, this is not good for your business...

In fact, it is enough to use only 2 API functions to make it possible to open a file selection dialog (Open or Save). These are the GetOpenFileName and GetSaveFileName functions for the library comdlg32.dll . And this DLL file is already available on all computers in the world, unlike the OCX.


It is best to use this simple module with a single universal function GetDialogFileName

Code:

---

Option Explicit
'////////////////////////////////////////////////////
'// A module for calling the file selection dialog //
'// Copyright (c) 08.05.2024 by HackerVlad        //
'// e-mail: [email protected]            //
'// Version 2.1                                    //
'////////////////////////////////////////////////////

Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameW" (ByVal pOpenfilename As Long) As Long
Private Declare Function GetSaveFileName Lib "comdlg32.dll" Alias "GetSaveFileNameW" (ByVal pOpenfilename As Long) As Long

Private Const OFN_ALLOWMULTISELECT = &H200
Private Const OFN_EXPLORER = &H80000
Private Const OFN_LONGNAMES = &H200000
Private Const OFN_NODEREFERENCELINKS = &H100000
Private Const OFN_OVERWRITEPROMPT = &H2
Private Const OFN_SHAREAWARE = &H4000

Private Type OPENFILENAME
    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    lpstrFilter As String
    lpstrCustomFilter As String
    nMaxCustFilter As Long
    nFilterIndex As Long
    lpstrFile As String
    nMaxFile As Long
    lpstrFileTitle As String
    nMaxFileTitle As Long
    lpstrInitialDir As String
    lpstrTitle As String
    flags As Long
    nFileOffset As Integer
    nFileExtension As Integer
    lpstrDefExt As String
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
End Type

Public Enum OpenFileEnum
    OFEOpenForLoad = 1
    OFEOpenForSave = 2
End Enum

' The file selection dialog function (open or save) returns the path ALWAYS without a slash at the end
' The array arrFiles is an optional parameter and is used only when selecting multiple files
Public Function GetDialogFileName(OpenMode As OpenFileEnum, ByVal strFilter As String, Optional ByVal hwndOwner As Long, Optional ByVal InitDir As String, Optional ByVal DialogTitle As String, Optional ByVal AllowMultiSelect As Boolean, Optional arrFiles As Variant) As String
    Dim ofn As OPENFILENAME
    Dim str As String
    Dim ret As Long
    Dim i As Long
   
    ofn.nMaxFile = 999999 ' The size of the buffer pointed to by lpstrFile, in characters. The buffer must be large enough to store a string or strings of the path and file name, including the terminating NULL character.
    ofn.hwndOwner = hwndOwner
    If Len(DialogTitle) > 0 Then ofn.lpstrTitle = DialogTitle
    ofn.lpstrFile = Space$(999999)
    ofn.lStructSize = LenB(ofn)
    ofn.lpstrFilter = strFilter
    If Len(InitDir) > 0 Then ofn.lpstrInitialDir = InitDir
    ofn.flags = OFN_SHAREAWARE Or OFN_LONGNAMES Or OFN_NODEREFERENCELINKS Or OFN_EXPLORER
   
    If OpenMode = OFEOpenForLoad Then
        If AllowMultiSelect = True Then ofn.flags = ofn.flags Or OFN_ALLOWMULTISELECT
        ret = GetOpenFileName(VarPtr(ofn))
    Else
        ofn.flags = ofn.flags Or OFN_OVERWRITEPROMPT
        ret = GetSaveFileName(VarPtr(ofn))
    End If
   
    If ret <> 0 Then
        If AllowMultiSelect = True And OpenMode = OFEOpenForLoad Then
            i = InStr(1, ofn.lpstrFile, vbNullChar & vbNullChar)
            If i Then ofn.lpstrFile = Left$(ofn.lpstrFile, i - 1)
           
            str = Left$(ofn.lpstrFile, ofn.nFileOffset - 1)
           
            ofn.lpstrFile = Right$(ofn.lpstrFile, Len(ofn.lpstrFile) - ofn.nFileOffset)
            arrFiles = Split(ofn.lpstrFile, vbNullChar)
           
            If Right$(str, 1) <> "\" Then
                GetDialogFileName = str
            Else
                GetDialogFileName = Left$(str, Len(str) - 1)
            End If
        Else
            i = InStr(1, ofn.lpstrFile, vbNullChar)
            If i Then GetDialogFileName = Left$(ofn.lpstrFile, i - 1)
        End If
    End If
End Function

---