Run Macro If Save Is Canceled On SaveAs Window In VBA

[Celtic_Avenger]

Can Anyone Help Me With This.

I have created this macro to create a new workbook from copying
single sheet to a new workbook.



Private Sub SaveLockout()

Dim LockOut

LockOut = MsgBox("Are You Sure You Wish To Create Your Lockout Reques
At This Time?", vbYesNo, "DAILY DRAT")
If LockOut = vbNo Then End

Sheets("Lockout").Select
Sheets("Lockout").Copy
ActiveWindow.DisplayHeadings = False

Dim fn As Variant
fn
Application.GetSaveAsFilename(InitialFileName:=Worksheets("Lockout").Range("J2").Value
_
FileFilter:="Excel Files (*.xls), *.xls", _
Title:="Save This Lockout Request Into Your Lockouts Folder! Do No
Change The File Name Shown!")
If TypeName(fn) = "Boolean" Then End
ActiveWorkbook.SaveAs fn
ActiveWorkbook.Close False
ActiveWorkbook.Sheets("Dispatch").Select
MsgBox "Your Lockout Request Has Been Saved And Closed." & Chr(13
& "Please Email To Your District Manager For Processing!"

End Sub


However.......This works a treat if the user does actually select Sav
at the SaveAs Window. If they select Cancel at this point, the macr
stops, and they are left with the new workbook still open. I would lik
it to close the new workook without saving it and then display a msg bo
saying "Save Canceled"

What do I need to add to do this?

Can it be done?

Thanks

Celtic_Avenger
:confused

 

[Tom Ogilvy]

Private Sub SaveLockout()

Dim LockOut

LockOut = MsgBox("Are You Sure You Wish To Create Your Lockout Request
At This Time?", vbYesNo, "DAILY DRAT")
If LockOut = vbNo Then End

Sheets("Lockout").Select
Sheets("Lockout").Copy
ActiveWindow.DisplayHeadings = False

Dim fn As Variant
fn =
Application.GetSaveAsFilename(InitialFileName:=Worksheets("Lockout").Range("
J2").Value,
_
FileFilter:="Excel Files (*.xls), *.xls", _
Title:="Save This Lockout Request Into Your Lockouts Folder! Do Not
Change The File Name Shown!")
If TypeName(fn) = "Boolean" Then
ActiveWorkbook.close SaveChanges:=False
msgbox "Cancelled"
exit sub
End if
ActiveWorkbook.SaveAs fn
ActiveWorkbook.Close False
ActiveWorkbook.Sheets("Dispatch").Select
MsgBox "Your Lockout Request Has Been Saved And Closed." & Chr(13)
& "Please Email To Your District Manager For Processing!"

End Sub

 

[Norman Jones]

Hi Celtic_Avenger,

In addition to Tom's code amendments, a small suggestion: replace End with
Exit Sub in your line:

If LockOut = vbNo Then End

An analogous change was effected by Tom when he revised a later portion of
your code. Unlike Exit Sub, End has the effect of resetting module level
variables and static local variables.