How to handle error when user entry for file name has illegal characters?

user_4948368 0
Viewed 54

I save the current worksheet as a PDF document using information input into range("Company_Name") to generate part of the file name.

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 if there an no special characters in the company name.

I am trying to create an error handler that issues the user with a msgbox to check the company name does not include any of the special characters.

error-handling vba excel
1 Answers
  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
user_185475290
Related