Run Macro If Save Is Canceled On SaveAs Window In VBA
 

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:=Wor ksheets("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: :confused: :confused: :confused: :confused

--
Celtic_Avenge
-----------------------------------------------------------------------
Celtic_Avenger's Profile: http://www.excelforum.com/member.php...fo&userid=1410
View this thread: http://www.excelforum.com/showthread.php?threadid=26134

Run Macro If Save Is Canceled On SaveAs Window In VBA
 

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:=Wor ksheets("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

--
Regards,
Tom Ogilvy

"Celtic_Avenger" wrote in
message ...

Can Anyone Help Me With This.

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



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:=Wor ksheets("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 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 Save
at the SaveAs Window. If they select Cancel at this point, the macro
stops, and they are left with the new workbook still open. I would like
it to close the new workook without saving it and then display a msg box
saying "Save Canceled"

What do I need to add to do this?

Can it be done?

Thanks

Celtic_Avenger
:confused: :confused: :confused: :confused: :confused:


--
Celtic_Avenger
------------------------------------------------------------------------
Celtic_Avenger's Profile:
http://www.excelforum.com/member.php...o&userid=14101
View this thread: http://www.excelforum.com/showthread...hreadid=261347

Run Macro If Save Is Canceled On SaveAs Window In VBA
 

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.
---
Regards,
Norman



"Tom Ogilvy" wrote in message
...
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:=Wor ksheets("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

--
Regards,
Tom Ogilvy