PDA

View Full Version : Exit Sub w/out Error when Cancelling Routine



YellowLabPro
10-04-2007, 12:16 AM
How to properly cancel a Sub?
I want to exit a sub upon selecting the cancel button or the upper right hand "X", the close button.
Currently I am receiving a run time error 1004.
Method SaveAs of object _Workbook Failed.

Pertinent Code:


fname = InputBox("File Name Saved", "Enter File Name to Save As", ThisWorkbook.Name _
& Chr(32) & Format(Date, "m-dd-yy"))
'emailtitle = Left(ThisWorkbook.Name, Find(".", ThisWorkbook.Name, 1) - 1)
emailtitle = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & " Orders"
'=LEFT(I233,FIND(".",I233,1)-1)
Range("A5:AF133").Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Columns("A:A").ColumnWidth = 2
Columns("B:E").EntireColumn.AutoFit
Columns("F:J").ColumnWidth = 11
Columns("K:AF").EntireColumn.AutoFit
Rows("1:6").RowHeight = 14.25
ActiveWorkbook.Names.Add Name:="TotalRow", RefersToR1C1:="=Sheet1!R133C1:R133C33"
Application.CutCopyMode = False
For x = Range("TotalRow").Row - 1 To 15 Step -1
Range("C" & x).EntireRow.Hidden = Len(Range("C" & x)) = 0
Next x
[b12].Select

Columns("B").ColumnWidth = 25
'Code Debugs Here
ActiveWorkbook.SaveAs Filename:=sPath & fname & ".xls"

rory
10-04-2007, 12:41 AM
Check if fname = "" after the InputBox statement and, if so, exit sub. You shouldn't be able to click the X while the sub is running!

YellowLabPro
10-04-2007, 05:05 AM
Change this up a little bit.
If either the Cancel button or the Close Button on any of the Inputbox responses are selected, then exit the sub.
The one anamoly is the store input box has values in the default.


store = InputBox("Enter (FF) Fairfax, (VB) VaBeach, (FV) for FF&VB, (EC) for ecboardco.", _
"Store Location", _
"Enter ( FF ) , ( VB ) , ( FV ) , ( EC ) ")

buyer = InputBox("Enter Buyer's Initials", "Buyer")
season = InputBox("Enter Season: Spring, Summer, Fall, Holiday", "Season")
year = InputBox("Enter 2 Digit Year; Ex: 08 for 2008", "Year")

rory
10-04-2007, 05:15 AM
If you press Cancel or the X, store will be "".