Force worksheet to NOT save
 

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
-----------------------------------------------------------

Thanks again for your help.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Force worksheet to NOT save
 

I think the culprit line is this one:

Else: Application.Quit
Try:

Else
ActiveWorkbook.Saved = True
Application.Quit

"R A" wrote:

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
-----------------------------------------------------------

Thanks again for your help.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Force worksheet to NOT save
 

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?



R A wrote:

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
-----------------------------------------------------------

Thanks again for your help.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

--

Dave Peterson

Force worksheet to NOT save
 

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!



R A wrote:

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
-----------------------------------------------------------

Thanks again for your help.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

--

Dave Peterson