Hi everyone,
I have some code that SHOULD ask the user to save the workbook with the beforeclose event. The message does come up, but the problem is, if the user selects "Yes" on the msgbox, the code saves, but right after that, excel asks with ITS message if the user wants to save the changes. I dont want the 2nd (excel's) save message to come up. Here is my code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set DataSheet = Worksheets("DataSheet")
opensheet = ""
'Call TestOpenSheets
Application.Run "Navigation.TestOpenSheets"
If Left(ActiveWorkbook.Name, 6) = "Retail" Then
'code
Else
'code
If Left(CurrFile, 6) = "Retail" Then Exit Sub
Application.ScreenUpdating = False
CurrFile = ActiveWorkbook.Name
'code
Application.DisplayAlerts = False
DataSheet.Delete
Worksheets("Navigation").Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
'Disables Counted Shutdown Before closing
Call Disable
ActiveWorkbook.Save
Else
Application.DisplayAlerts = False
DataSheet.Delete
Worksheets("Navigation").Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
'Disables Counted Shutdown Before closing
Call Disable
ActiveWorkbook.Saved = True
End If
ActiveWorkbook.Saved = True
End If
End Sub
Why is the message coming up? i am telling excel the workbook IS saved, yet it still asks??
Can i accomplish this with application.displayalerts=false? If i use this, does the file save or NOT save when overriding? also, do i have to turn alerts back on or do they automatically come back on? (for example if i have more than one file open and i want alerts ON in that one)
Thanks
I have some code that SHOULD ask the user to save the workbook with the beforeclose event. The message does come up, but the problem is, if the user selects "Yes" on the msgbox, the code saves, but right after that, excel asks with ITS message if the user wants to save the changes. I dont want the 2nd (excel's) save message to come up. Here is my code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set DataSheet = Worksheets("DataSheet")
opensheet = ""
'Call TestOpenSheets
Application.Run "Navigation.TestOpenSheets"
If Left(ActiveWorkbook.Name, 6) = "Retail" Then
'code
Else
'code
If Left(CurrFile, 6) = "Retail" Then Exit Sub
Application.ScreenUpdating = False
CurrFile = ActiveWorkbook.Name
'code
Application.DisplayAlerts = False
DataSheet.Delete
Worksheets("Navigation").Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
'Disables Counted Shutdown Before closing
Call Disable
ActiveWorkbook.Save
Else
Application.DisplayAlerts = False
DataSheet.Delete
Worksheets("Navigation").Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
'Disables Counted Shutdown Before closing
Call Disable
ActiveWorkbook.Saved = True
End If
ActiveWorkbook.Saved = True
End If
End Sub
Why is the message coming up? i am telling excel the workbook IS saved, yet it still asks??
Can i accomplish this with application.displayalerts=false? If i use this, does the file save or NOT save when overriding? also, do i have to turn alerts back on or do they automatically come back on? (for example if i have more than one file open and i want alerts ON in that one)
Thanks