close a "do you want to save box"

royboy531

Board Regular
Joined
Nov 11, 2005
Messages
52
in middle of my macro I open a new book and at the end of the macro I close it but in the macro the pop up box that asks "save before close" comes up. I need a code to auto select no to close the box. anyone have any ideas. thanks
 

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
You need to add a line of code to save the workbook with either SaveChanges:=True or SaveChanges:=False.
 

royboy531

Board Regular
Joined
Nov 11, 2005
Messages
52
I still can't seem to get it to work. when I put in SaveChanges:=True or SaveChanges:=False I get a compile error: expected: expression. I tried taking the colon out, didn't work. I also tried puttig save instead of savechanges, didn't work. any other suggestions??? thanks for the help.
[/code]
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,288
Can you post the code?

You might try this.
Code:
Application.DisplayAlerts= False

' your code here

Application.DisplayAlerts= True
By the way where did you put Brian's suggestion?
 

royboy531

Board Regular
Joined
Nov 11, 2005
Messages
52
Norie,
I was putting Brian's code after Activewindow.close. For your suggestion am I supposed to put Brian's code between yours or part of my code?? I am a bit confused????
thanks
royboy531
 

royboy531

Board Regular
Joined
Nov 11, 2005
Messages
52
here it is Norie
Code:
Sub macro6()
UserForm1.Show
End Sub

Private Sub btnFilter_Click()

    Dim startDate As String
    Dim shift As String
    
    'reads date from the userform
    startDate = tbstartdate.Value 'tbStartDate is the name of the textbox for Starting Date
    shift = tbshift 'tbshift is the name of the textbox for shift
    
    'FILTERS
    With Worksheets("sheet1")
        .AutoFilterMode = False
        .Range("a1:p1").AutoFilter
        .Range("a1:p1").AutoFilter Field:=2, Criteria1:=shift
        .Range("a1:p1").AutoFilter Field:=1, Criteria1:=(">=" & startDate), _
            Operator:=xlAnd, Criteria2:=("<=" & startDate)
       UserForm1.Hide
        Range("A1:P" & Cells(Rows.Count, "A").End(xlUp).Row).Select
End With
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Key2:=Range("C2") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom
    Selection.Subtotal GroupBy:=3, Function:=xlAverage, TotalList:=Array(6, 7, _
        8, 9), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    Range("a1").Select
    Range("A1:P" & Cells(Rows.Count, "C").End(xlUp).Row).Select
    With Selection.Font
        .Size = 9
    Selection.AutoFormat Format:=xlRangeAutoFormatSimple, Number:=True, Font _
        :=True, Alignment:=True, Border:=True, Pattern:=True, Width:=True
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Range("A1:P1").Select
    With Selection.Font
    .Size = 8.5
    End With
    With ActiveSheet.PageSetup
        .CenterHeader = "&D  &T"
        .LeftMargin = Application.InchesToPoints(0.5)
        .RightMargin = Application.InchesToPoints(0.5)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = True
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintArea = ActiveSheet.UsedRange.Address
    End With
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    ActiveWindow.Close
    Application.DisplayAlerts = False
    Savechanges = True
    Application.DisplayAlerts = True
    Range("a1").Select
    Selection.AutoFilter
    Selection.AutoFilter


End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,288
This has both Brian and I's suggestions.
Code:
Private Sub btnFilter_Click()
Dim startDate As String
Dim shift As String
    
    'reads date from the userform
    startDate = tbstartdate.Value 'tbStartDate is the name of the textbox for Starting Date
    shift = tbshift 'tbshift is the name of the textbox for shift
    
    Application.DisplayAlerts = False
    
    'FILTERS
    With Worksheets("sheet1")
        .AutoFilterMode = False
        .Range("a1:p1").AutoFilter
        .Range("a1:p1").AutoFilter Field:=2, Criteria1:=shift
        .Range("a1:p1").AutoFilter Field:=1, Criteria1:=(">=" & startDate), _
            Operator:=xlAnd, Criteria2:=("<=" & startDate)
       UserForm1.Hide
        Range("A1:P" & Cells(Rows.Count, "A").End(xlUp).Row).Copy
    End With
    
    Workbooks.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Key2:=Range("C2") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom
    Selection.Subtotal GroupBy:=3, Function:=xlAverage, TotalList:=Array(6, 7, _
        8, 9), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    
    With Range("A1:P" & Cells(Rows.Count, "C").End(xlUp).Row)
        Font.Size = 9
        .AutoFormat Format:=xlRangeAutoFormatSimple, Number:=True, Font _
        :=True, Alignment:=True, Border:=True, Pattern:=True, Width:=True
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    
    With Range("A1:P1").Font
        .Size = 8.5
    End With
    
    With ActiveSheet.PageSetup
        .CenterHeader = "&D  &T"
        .LeftMargin = Application.InchesToPoints(0.5)
        .RightMargin = Application.InchesToPoints(0.5)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = True
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintArea = ActiveSheet.UsedRange.Address
    End With
    
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    
    ActiveWindow.Close Savechanges:=True
    
    Range("A1").AutoFilter
    Range("A1").AutoFilter

    Application.DisplayAlerts = True
    
End Sub
 

Forum statistics

Threads
1,078,428
Messages
5,340,181
Members
399,358
Latest member
forumas

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top