I have a code below that saves the current worksheet as a PDF document. It uses the information input into range("Customer_Name") to generate part of the file name of the PDF. An issue I am coming across is whereby the user uses special characters that are prohibited in filenames (& " ? < > # { } % ~ / \ ).

'Save Copy of Order Form in PDF Format
Dim path As String
Dim fname As String

fname = Sheets("ORDER FORM").Range("Company_Name").Value & " - " & Format(Now(), "dd-mm-yyyy hhmmss")
path = Application.ActiveWorkbook.path

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=path & "\" & fname & ".pdf", _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False

Application.ScreenUpdating = True

'Completion MsgBox
MsgBox "Order has saved in PDF Format and stored in file:" & vbNewLine & path, vbInformation   vbOKOnly, "File Saved"

On Error GoTo ErrorHandler

ErrorHandler:
MsgBox "Please check that the company name does not include any of the characters highlighted in the popup box", vbCritical
 Resume

This code works fine if there an no special characters used in the company name.

What I am trying to do is create an error handler that issues the user with a msgbox prompting them to check the company name does NOT include any of the special characters.

I have written in the above error handler and then purposely used one of the special characters but am still getting the debug msg. Please can anybody assist?

CodePudding user response：

1. Your On Error GoTo ErrorHandler needs to be at the top of your sub, or at least before where the error will occur.
2. I recommend just doing a substring check similar to below rather than an error handler, since the error handler captures any kind of error that can occur.

If InStr(1, path, "/", vbTextCompare) = 0 Then
 MsgBox "Contains illegal characters"
 End Sub
End If