Consulting

Results 1 to 4 of 4

Thread: Exit Sub w/out Error when Cancelling Routine

  1. #1

    Exit Sub w/out Error when Cancelling Routine

    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:

    [VBA]
    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"
    [/VBA]
    my site: www.ecboardco.com
    was built w/ a majority of the assistance from the board members here... thanks VBAX.

    Just because I see something, doesn't mean that what's actually happening is what I see.

    You don't get from 0-90 by standing still!

  2. #2
    VBAX Master
    Joined
    Jun 2007
    Location
    East Sussex
    Posts
    1,110
    Location
    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!
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    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.

    [VBA]
    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")
    [/VBA]
    my site: www.ecboardco.com
    was built w/ a majority of the assistance from the board members here... thanks VBAX.

    Just because I see something, doesn't mean that what's actually happening is what I see.

    You don't get from 0-90 by standing still!

  4. #4
    VBAX Master
    Joined
    Jun 2007
    Location
    East Sussex
    Posts
    1,110
    Location
    If you press Cancel or the X, store will be "".
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •