User hits Cancel


The Inspector

Here is a function I am attempting to use. It seemed to work fine until I
attempted to account for the user hitting cancel when prompted to enter a
file name. I get a type mismatch error because the cancel button returns a
boolean instead of a string. How do I rectify this? Am I going about this all
wrong? Any help is appreciated.

Function SaveToPDF() As Boolean

Dim folderName As String
Dim fileName As String
Dim namingError
fileName = ""

folderName = BrowseFolder(Caption:="Select A Folder",
InitialFolder:="C:\Documents and Settings\Jamie\Desktop\")

If folderName = "" Then
Exit Function ' User hit Cancel

While fileName = ""
fileName = Application.InputBox("Enter a name for the PDF file.")
If fileName = False Then
SaveToPDF = False
Exit Function 'user hit cancel
If fileName = "" Then
namingError = MsgBox("You must enter a name for the file
or click Cancel to exit. Click OK to" _
+ " return to the name entry screen", vbOKOnly, "File
Naming Error")
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF,
fileName:= _
folderName + "\" + fileName + ".pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True,
IgnorePrintAreas:=False, _
SaveToPDF = True
End If
End If
End If
Application.WindowState = xlNormal

End Function

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question