Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -- 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -- 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"this operation has been canceled due to restrictions in effect on | Excel Discussion (Misc queries) | |||
how to save a desired window size but hv window comeup fullsz by d | Excel Discussion (Misc queries) | |||
Disabling SaveAs and Save? | Excel Discussion (Misc queries) | |||
Detecting Save vs SaveAs in BeforeSave | Excel Programming | |||
How to call File Open / Save Window in a Macro | Excel Programming |