Add Instruction Code For NO or CANCEL within ActiveWorkbook.SaveAs

[bdb1974]

Hi all,

When my code runs, it tries to create individual booklets from each sheet.
If a booklet exists with the same name of the booklet its trying to create, a
message window pops up, giving the user the option to select from (SAVE,NO,CANCEL).

If the user selects SAVE, it overwrites the existing file.
If the user slects NO, the code errors out.
If the user selects CANCEL, the code errors out.

I need some code that will be able to handle the NO and CANCEL operations.

I'm struggling on adding this to my code.

I'd like for there to be a SAVE_AS if the user selects NO.
I'd like for there to be a Exit Sub, if the user selects CANCEL

Sub SaveUpdatedBOM()
Dim Cancel As Boolean
  'Split worksheets in current workbook into
  ' many separate workbooks  D.McRitchie, 2004-06-12
  'Close each module  AND the VBE before running to save time
  ' provides a means of seeing how big sheets really are
  'Hyperlinks and formulas pointing to other worksheets within
  ' the original workbook will usually be unuseable in the new workbooks.
    Dim CurWkbook As Workbook
    Dim wkSheet As Worksheet
    Dim newWkbook As Workbook
    Dim wkSheetName As String
    Dim shtcnt(3) As Long
    Dim xpathname As String, dtimestamp As String
    dtimestamp = Format(Now, "yyyymmdd_hhmmss")
    xpathname = Sheets("BOM").Range("C18").Value & "\"

    Set CurWkbook = Application.ActiveWorkbook
    
    shtcnt(2) = ActiveWorkbook.Sheets.Count
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
      If Cancel = True Then
        Exit Sub
         Else
    For Each wkSheet In CurWkbook.Worksheets
      shtcnt(1) = shtcnt(1) + 1
  
      Application.StatusBar = shtcnt(1) & "/" & shtcnt(2) & _
          "  " & wkSheet.Name
      wkSheetName = Trim(wkSheet.Name)
      If wkSheetName = Left(Application.ActiveWorkbook.Name, _
         Len(Application.ActiveWorkbook.Name) - 4) Then _
         wkSheetName = wkSheetName & "_D" & dtimestamp
      Workbooks.Add
      
      
         ActiveWorkbook.SaveAs _
         Filename:=xpathname & wkSheetName & ".xls", _
         FileFormat:=xlNormal, Password:="", _
         WriteResPassword:="", CreateBackup:=False, _
         ReadOnlyRecommended:=False
         
      
        
         
      Set newWkbook = ActiveWorkbook
      
      Application.DisplayAlerts = False
      newWkbook.Worksheets("sheet1").Delete
      On Error Resume Next
      newWkbook.Worksheets(wkSheet.Name).Delete
      On Error GoTo 0
      Application.DisplayAlerts = True
      
      CurWkbook.Worksheets(wkSheet.Name).Copy Before:=newWkbook.Sheets(1)
      'no duplicate sheet1 because they begin with "a"
      ActiveWorkbook.Save
      ActiveWorkbook.Close
    Next wkSheet
    Application.StatusBar = False      'return control to Excel
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
  Kill (xpathname & "MASTER.xls")
   Kill (xpathname & "BOM_INSERT.xls")
   

 RemoveAnyMacros2
 BringToFrontAndReformatBOM
 
  Range("A1").Select
  Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic

'ChangeFilename
  MsgBox "Done"
  End If
    
End Sub

Any help with this is greatly appreciated.


BDB