Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

How to create an error handler for replacing debug msg with a MsgBox

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.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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?

>Solution :

  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
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading