Force worksheet to NOT save

  • Thread starter Thread starter R A
  • Start date Start date
R

R A

Hello

I have a frustrating situation, which several kind ppl have already
provided their input (thankyou).
However, I was wondering if someone might be able to examine the code
below, and tell me why when I exit a workbook running macros it still
asks me if I want to save? My goal is for this NOT to happen.
-----------------------------------------------------------
Sub workbook_before_close()
Worksheets("Error").Visible = xlSheetVisible
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Visible = (ws.Name <> "Error") * -1 - 1
Next ws
Application.DisplayAlerts = False
Application.SaveWorkspace
Application.DisplayAlerts = True
End Sub
-----------------------------------------------------------
Sub workbook_open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
Worksheets("Error").Visible = xlVeryHidden
Application.Sheets("Sheet1").Select
If Now >= Worksheets("Error").Range("AO241") Then
Confirm = MsgBox("This workbook has expired. Please contact support
for further assistance.", vbInformation + vbOKOnly, "Workbook Expiry")
If Confirm = vbOKOnly Then
Application.Quit
Else: Application.Quit
End If
End If
ThisWorkbook.Saved = True
Application.DisplayAlerts = False
End Sub
-----------------------------------------------------------
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
MsgBox "Sorry, but the save functions of this workbook have been
disabled."
Cancel = True
End Sub
 
I think the culprit line is this one:

Else: Application.Quit
Try:

Else
ActiveWorkbook.Saved = True
Application.Quit
 
You have a typo in the workbook_before_close name:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Worksheets("Error").Visible = xlSheetVisible
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Visible = (ws.Name <> "Error") * -1 - 1
Next ws
With Application
.EnableEvents = False
.DisplayAlerts = False
.SaveWorkspace
.DisplayAlerts = True
.EnableEvents = True
End With
End Sub

And .saveworkspace will invoke workbook_beforesave unless you tell it not to.

I've never really used .saveworkspace. Did you really want to use that?
 
And one more thing.

Application.quit

Seems kind of excessive to me. If I've just changed a different workbook and
haven't saved and don't notice that you're closing the application, you may lose
more friends/customers/clients than you want!
 
Back
Top