Cancel option when saving

[eli_m]

Hi,

With the code at the bottom how can I make it so it does NOT do:

Range("H4").Copy
Range("H4").PasteSpecial xlPasteValuesAndNumberFormats ' Removes H4 formula

If the user hits cancel when saving?

Sub SaveQuoteAsWorkbook()

Range("H4").Copy
Range("H4").PasteSpecial xlPasteValuesAndNumberFormats ' Removes H4 formula

Dim tDate As String
Dim FileSaveName As String
Dim fName As String

fName = "Quote " & Range("B66")
FileSaveName = CStr(Application.GetSaveAsFilename(InitialFileName:=fName, filefilter:="Excel Files(*.xlsm),*.xlsm", Title:="Please save the file"))

If FileSaveName <> "False" Then ActiveWorkbook.SaveAs Filename:=FileSaveName, FileFormat:=52

End Sub

Thanks in advance!

 

[Skyybot]

What does the SaveQuoteAsWorkbook Sub get called from?

 

[eli_m]

What does the SaveQuoteAsWorkbook Sub get called from?

A button:

Just to be clear, I am talking about Cancel button button when saving a document:

 

[Skyybot]

Try changing this:

FileSaveName = CStr(Application.GetSaveAsFilename(InitialFileName:=fName, filefilter:="Excel Files(*.xlsm),*.xlsm", Title:="Please save the file"))

To This:

FileSaveName = Application.GetSaveAsFilename(InitialFileName:=fName, filefilter:="Excel Files(*.xlsm),*.xlsm", Title:="Please save the file")

 

[eli_m]

Try changing this:

FileSaveName = CStr(Application.GetSaveAsFilename(InitialFileName:=fName, filefilter:="Excel Files(*.xlsm),*.xlsm", Title:="Please save the file"))

To This:

FileSaveName = Application.GetSaveAsFilename(InitialFileName:=fName, filefilter:="Excel Files(*.xlsm),*.xlsm", Title:="Please save the file")

I did this:

Sub SaveQuoteAsWorkbook()

Range("H4").Copy
Range("H4").PasteSpecial xlPasteValuesAndNumberFormats ' Removes H4 formula

Dim tDate As String
Dim FileSaveName As String
Dim fName As String

fName = "Quote " & Range("B66")
FileSaveName = Application.GetSaveAsFilename(InitialFileName:=fName, filefilter:="Excel Files(*.xlsm),*.xlsm", Title:="Please save the file")

If FileSaveName <> "False" Then ActiveWorkbook.SaveAs Filename:=FileSaveName, FileFormat:=52

End Sub

but it still clears H4 when I press Cancel because of:

Range("H4").Copy
Range("H4").PasteSpecial xlPasteValuesAndNumberFormats ' Removes H4 formula

I only want it to Copy then paste when I press "Save" - I want it not to run the above code if I press Cancel here:

 

[Skyybot]

Try this

Sub SaveQuoteAsWorkbook()

Dim tDate As String
Dim FileSaveName As String
Dim fName As String

fName = "Quote " & Range("B66")
FileSaveName = CStr(Application.GetSaveAsFilename(InitialFileName:=fName, filefilter:="Excel Files(*.xlsm),*.xlsm", Title:="Please save the file"))

If FileSaveName <> "False" Then ActiveWorkbook.SaveAs Filename:=FileSaveName, FileFormat:=52
Else
Range("H4").Copy
Range("H4").PasteSpecial xlPasteValuesAndNumberFormats ' Removes H4 formula
End IF

End Sub

 

[Skyybot]

Try this

Sub SaveQuoteAsWorkbook()

Dim tDate As String
Dim FileSaveName As String
Dim fName As String

fName = "Quote " & Range("B66")
FileSaveName = CStr(Application.GetSaveAsFilename(InitialFileName:=fName, filefilter:="Excel Files(*.xlsm),*.xlsm", Title:="Please save the file"))

If FileSaveName <> "False" Then ActiveWorkbook.SaveAs Filename:=FileSaveName, FileFormat:=52
Else
Range("H4").Copy
Range("H4").PasteSpecial xlPasteValuesAndNumberFormats ' Removes H4 formula
End IF

End Sub

Sorry. Not tested. I may have to change the IF statement a little.

 

[Skyybot]

Yeah, my bad. This should work.

Sub SaveQuoteAsWorkbook()

Dim tDate As String
Dim FileSaveName As String
Dim fName As String

fName = "Quote " & Range("B66")
FileSaveName = CStr(Application.GetSaveAsFilename(InitialFileName:=fName, filefilter:="Excel Files(*.xlsm),*.xlsm", Title:="Please save the file"))

If FileSaveName <> "False" Then ActiveWorkbook.SaveAs Filename:=FileSaveName, FileFormat:=52
Range("H4").Copy
Range("H4").PasteSpecial xlPasteValuesAndNumberFormats ' Removes H4 formula
Else
Exit Sub
End IF

End Sub

 

[eli_m]

Yeah, my bad. This should work.

Sub SaveQuoteAsWorkbook()

Dim tDate As String
Dim FileSaveName As String
Dim fName As String

fName = "Quote " & Range("B66")
FileSaveName = CStr(Application.GetSaveAsFilename(InitialFileName:=fName, filefilter:="Excel Files(*.xlsm),*.xlsm", Title:="Please save the file"))

If FileSaveName <> "False" Then ActiveWorkbook.SaveAs Filename:=FileSaveName, FileFormat:=52
Range("H4").Copy
Range("H4").PasteSpecial xlPasteValuesAndNumberFormats ' Removes H4 formula
Else
Exit Sub
End IF

End Sub

Thanks for your help but I am getting this error:

 

[Skyybot]

Small change in Syntax

If FileSaveName <> "False" Then 
      ActiveWorkbook.SaveAs Filename:=FileSaveName, FileFormat:=52
       Range("H4").Copy
       Range("H4").PasteSpecial xlPasteValuesAndNumberFormats ' Removes H4 formula
Else
      Exit Sub
End IF