input msg error when "Cancel" button is clicked --

[Ajohneus]

Hi Sir,

I'm working on something where in the workbook is saved on the deskstop using macro. Saving works just fine. However, when I clicked the cancel button from the input msg I received a runtime error. And when i debug, it points to this path: ThisWorkbook.Saveas (Environ("userprofile") & Application.PathSeparator & "Desktop" & Application.PathSeparator & Filename)

I'm I doing something wrong with the code? Appreciate any help on this.

Thanks,
AJ

Sub Saveas()
'
' Saveas Macro
'
Dim Filenae As Variant
'Dim Filename




Filename = InputBox("ENTER FILE NAME BELOW: (FORMAT: SHOW + MM-DD-YYYY)")
'Macro running but when I click cancel from the input box this error pop up.
'Run-time error '1004':
'The file could not be accessed.
'Make sure the specified folder exists.
'Make sure the folder that contains the file is no read-only
'make sure the file name does not contain any of the following characters: <> ? [] | or *
'make sure the file/path name doesn't contain more than 218

ThisWorkbook.Saveas (Environ("userprofile") & Application.PathSeparator & "Desktop" & Application.PathSeparator & Filename)

[GTO]

When you click the 'Cancel' button on the InputBox, it returns an empty string.

You may wish to try the GetSaveAs Dialog. By short/crummy example...


Option Explicit

Sub Test()
Dim MySuggestedFileName As String
Dim lFileFormat As Long

MySuggestedFileName = Application.GetSaveAsFilename(FileFilter:="Excel Workbook (*.xls;*.xlsm), *.xls;xlsm")

If Not UCase$(MySuggestedFileName) = "FALSE" Then
If LCase$(Right$(MySuggestedFileName, 1)) = "m" Then
lFileFormat = 52
ElseIf LCase$(Right$(MySuggestedFileName, 1)) = "s" Then
lFileFormat = 56
Else
MsgBox "Oops, some file format i didn't include... Now exiting", vbInformation, vbNullString
Exit Sub
End If

ThisWorkbook.SaveAs MySuggestedFileName, lFileFormat
End If

End Sub

Hope that helps,

Mark

[Ajohneus]

Hi Mark,

This is awesome! I've tried your suggestion and it work just fine. It a different approach but would do just fine. :)

Appreciate it.

Thanks,AJ

[Ajohneus]

Thanks Mark. Issue resolved. Have a good one!